Re: [GENERAL] Huge backends / self joins / long queries

Hello,

I had the same trouble! And It's why I install the version 6.5 of postgres. In the 6.4, you cannot make some INTERSECT.
Try to use the Intersection It's very powerfull

JK

Dirk Lutzebaeck wrote:

> 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=1000 width=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
>
> ************