Thread: Huge backends / self joins / long queries
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
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=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 > > ************