6.1 vs 6.3.2 performance
От | Bill Moore |
---|---|
Тема | 6.1 vs 6.3.2 performance |
Дата | |
Msg-id | 199811061935.LAA01048@fsr.com обсуждение исходный текст |
Список | pgsql-general |
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 =============================================
В списке pgsql-general по дате отправления: