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 по дате отправления:

Предыдущее
От: Fabio Mancinelli
Дата:
Сообщение: Re: [GENERAL] PostgreSQL 6.4 problems on Linux.
Следующее
От: Dustin Sallings
Дата:
Сообщение: view problem