Huge backends / self joins / long queries - Mailing list pgsql-general

Hi,

I don't know if this fits into the current thread but I have also a
problem with backends growing up to 100MB and more. This is on 6.5:


CREATE TABLE docobj (
    envelope    OID NOT NULL,
        docspec        OID,
    docindex    INTEGER,
        attrid        INTEGER,
    val_int        INTEGER,
    val_str        TEXT
);

This table saves values with differnet base types for doing self
joins. A typical select is the following:

SELECT DISTINCT d0.envelope, d0.docindex
FROM envelope e0, docobj d0,
     envelope e1, docobj d1,
     envelope e2, docobj d2,
     envelope e3, docobj d3
WHERE d0.docspec=734675 AND d0.envelope=e0.oid AND d0.attrid=0 AND
      d0.val_int='700050' AND d0.docindex=d0.docindex AND e0.oid=e0.oid AND

      d1.docspec=734675 AND d1.envelope=e1.oid AND d1.attrid=1 AND
      d1.val_str='01' AND d1.docindex=d0.docindex AND e1.oid=e0.oid AND

      d2.docspec=734675 AND d2.envelope=e2.oid AND d2.attrid=5 AND
      d2.val_str='00' AND d2.docindex=d0.docindex AND e2.oid=e0.oid AND

      d3.docspec=734675 AND d3.envelope=e3.oid AND d3.attrid=6 AND
      d3.val_str='UG' AND d3.docindex=d0.docindex AND e3.oid=e0.oid

ORDER BY boxinfo.time DESC

Indices are generated on all joined attributes.

This particular select needs 38MB on the backend. There is not much
data. Docobj has 1300 rows. This query is a bit of a fake. The real
query takes some additional joins with other tables. So the
backend reaches 100Mb.

I have also set pg_geqo to

Pool_Size            128
Effort               low
Generations          200
Random_Seed          830518260
Selection_Bias       1.750000


EXPLAIN on the query above gives:

Unique  (cost=59.40 rows=1000 width=52)
 -> Sort (cost=59.40 rows=1000 width=52)
    -> Nested Loop (cost=59.40 rows=1000 width=52)
       -> Nested Loop (cost=16.40 rows=1 width=48)
          -> Index Scan using docobj_spec_index on docobj d2 (cost=2.05 rows=1 width=8)
          -> Seq Scan (cost=14.35 rows=2 width=40)
             -> ??? (cost=14.35 rows=2 width=40)
                -> Nested Loop (cost=14.35 rows=2 width=40)
                   -> Nested Loop (cost=12.30 rows=1 width=36)
                      -> Index Scan using docobj_spec_index on docobj d3 (cost=2.05 rows=1 width=8)
                      -> Seq Scan (cost=10.25 rows=2 width=28)
                         -> ??? (cost=10.25 rows=2 width=28)
                            -> Nested Loop (cost=10.25 rows=2 width=28)
                               -> Nested Loop (cost=8.20 rows=1 width=24)
                                  -> Nested Loop (cost=6.15 rows=1 width=20)
                                     -> Index Scan using docobj_spec_index on docobj d0 (cost=2.05 rows=1 width=8)
                                     -> Seq Scan (cost=4.10 rows=2 width=12)
                                        -> ??? (cost=4.10 rows=2 width=12)
                                           -> Nested Loop (cost=4.10 rows=2 width=12)
                                              -> Index Scan using docobj_spec_index on docobj d1 (cost=2.05 rows=1
width=8)
                                              -> Index Scan using envelope_oid_index on envelope e1 (cost=2.05
rows=1000width=4) 
                                  -> Index Scan using envelope_oid_index on envelope e0 (cost=2.05 rows=101 width=4)
                               -> Index Scan using envelope_oid_index on envelope e3 (cost=2.05 rows=1000 width=4)
                   -> Index Scan using envelope_oid_index on envelope e2 (cost=2.05 rows=1000 width=4)
       -> Seq Scan on boxinfo (cost=43.00 rows=1000 width=4)


So, I guess it has something todo with the optimizer. What should I do?

Thanks for help,

Dirk