I have a C++ interface between a web site and a postgres DB. Website
uses cgi to dump the query to the C++ app which connects to postgres,
submits result and collects reply which it uses to draw the next HTML
page. All was well in 6.1, but modifications requiring subqueries
dictated that I move up to 6.3.2 (newest at the time). Suddenly
queries formerly taking 4 seconds are now taking 8min, 20sec to
complete on identical data sets. Perhaps I've misconfigured
something. I've included printout from an EXPLAIN below--I wish I
could interpret the difference in the EXPLAINs. Although the 6.1
cost is higher than the 6.3.2, 6.1 is using a hash join rather than
the nested nested-loops in 6.3.2.
I've not been able to debug given exiting manpages and FAQ
documentation. Perhaps someone more Postgres or db conversant can
shed some light or point me in the right direction. Thanks in advance
for any and all enlightenment offered.
schema:
table rishtml = 26,782 records of 22 columns
table keywords = 451,164 records of 2 columns, ref_id col maps to rishtml.f0
table authors = 40,573 records of 2 columns, ref_id col maps to rishtml.f0
test query is
select rishtml.f0 from keywords K1, keywords K2, rishtml where
(K1.keyword = 'accipiter' and K2.keyword = 'nest')
and
K1.ref_id = rishtml.f0
and
K2.ref_id = rishtml.f0
;
using PostgreSQL 6.3.2 requires 8min 20 sec to complete:
raptor=> \i s2
explain select rishtml.f0 from keywords K1, keywords K2, rishtml where
(K1.keyword = 'accipiter' and K2.keyword = 'nest')
and
K1.ref_id = rishtml.f0
and
K2.ref_id = rishtml.f0
;
NOTICE: QUERY PLAN:
Nested Loop (cost=6.15 size=1 width=12)
-> Nested Loop (cost=4.10 size=1 width=8)
-> Index Scan on k2 (cost=2.05 size=1 width=4)
-> Index Scan on rishtml (cost=2.05 size=26166 width=4)
-> Index Scan on k1 (cost=2.05 size=1 width=4)
EXPLAIN
EOF
same query on 6.1 takes only 4 seconds.
explain select rishtml.f0 from keywords K1, keywords K2, rishtml where
(K1.keyword = 'accipiter' and K2.keyword = 'nest')
and
K1.ref_id = rishtml.f0
and
K2.ref_id = rishtml.f0
;
NOTICE:QUERY PLAN:
Hash Join (cost=10.37 size=1 width=12)
-> Nested Loop (cost=6.15 size=3 width=8)
-> Index Scan on k2 (cost=2.05 size=2 width=4)
-> Index Scan on rishtml (cost=2.05 size=26166 width=4)
-> Hash (cost=0.00 size=0 width=0)
-> Index Scan on k1 (cost=2.05 size=2 width=4)
EXPLAIN
EOF
=============================================
Bill Moore bmoore@fsr.com
System Administrator First Step Research
(208) 882-8869 Moscow, Idaho 83843
=============================================
