specific query (not all) on Pg8 MUCH slower than Pg7 - Mailing list pgsql-performance

Hi,

Despite numerous efforts, we're unable to solve a severe performance limitation between Pg 7.3.2
and Pg 8.1.4.

The query and 'explain analyze' plan below, runs in
    26.20 msec on Pg 7.3.2, and
    2463.968 ms on Pg 8.1.4,

and the Pg7.3.2 is on older hardware and OS.

Multiply this time difference by >82K, and a 10 minute procedure (which includes
this query), now runs in 10 *hours*.....not good....

In general, however, we're pleased with performance of this very same Pg8.1.4 server
as compared to the Pg7.3.2 server (loading/dumping, and other queries are much faster).

QUERY:

SELECT dx.db_id, dx.accession, f.uniquename, f.name, cvt.name as ntype,
        fd.is_current
from feature f, feature_dbxref fd, dbxref dx, cvterm cvt
where fd.dbxref_id = dx.dbxref_id
        and fd.feature_id = f.feature_id
        and f.type_id = cvt.cvterm_id
        and accession like 'AY851043%'
        and cvt.name not in
('gene','protein','natural_transposable_element','chromosome_structure_variation','chromosome_arm','repeat_region')
;


explain analyze output on Pg7.3.2:

-----------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..23.45 rows=1 width=120) (actual time=25.59..25.59 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..17.49 rows=1 width=82) (actual time=25.58..25.58 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..11.93 rows=1 width=30) (actual time=25.58..25.58 rows=0 loops=1)
               ->  Index Scan using dbxref_idx2 on dbxref dx  (cost=0.00..5.83 rows=1 width=21) (actual
time=25.58..25.58rows=0 loops=1) 
                     Index Cond: ((accession >= 'AY851043'::character varying) AND (accession < 'AY851044'::character
varying))
                     Filter: (accession ~~ 'AY851043%'::text)
               ->  Index Scan using feature_dbxref_idx2 on feature_dbxref fd  (cost=0.00..6.05 rows=5 width=9) (never
executed)
                     Index Cond: (fd.dbxref_id = "outer".dbxref_id)
         ->  Index Scan using feature_pkey on feature f  (cost=0.00..5.54 rows=1 width=52) (never executed)
               Index Cond: ("outer".feature_id = f.feature_id)
   ->  Index Scan using cvterm_pkey on cvterm cvt  (cost=0.00..5.94 rows=1 width=38) (never executed)
         Index Cond: ("outer".type_id = cvt.cvterm_id)
         Filter: ((name <> 'gene'::character varying) AND (name <> 'protein'::character varying) AND (name <>
'natural_transposable_element'::charactervarying) AND (name <> 'chromosome_structure_variation'::character varying) AND
(name<> 'chromosome_arm'::character varying) AND (name <> 'repeat_region'::character varying)) 
 Total runtime: 26.20 msec
(14 rows)

========


explain analyze output on Pg8.1.4:

-----------------------------------------------------------------
 Nested Loop  (cost=0.00..47939.87 rows=1 width=108) (actual time=2463.654..2463.654 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..47933.92 rows=1 width=73) (actual time=2463.651..2463.651 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..47929.86 rows=1 width=22) (actual time=2463.649..2463.649 rows=0 loops=1)
               ->  Seq Scan on dbxref dx  (cost=0.00..47923.91 rows=1 width=21) (actual time=2463.646..2463.646 rows=0
loops=1)
                     Filter: ((accession)::text ~~ 'AY851043%'::text)
               ->  Index Scan using feature_dbxref_idx2 on feature_dbxref fd  (cost=0.00..5.90 rows=4 width=9) (never
executed)
                     Index Cond: (fd.dbxref_id = "outer".dbxref_id)
         ->  Index Scan using feature_pkey on feature f  (cost=0.00..4.05 rows=1 width=59) (never executed)
               Index Cond: ("outer".feature_id = f.feature_id)
   ->  Index Scan using cvterm_pkey on cvterm cvt  (cost=0.00..5.94 rows=1 width=43) (never executed)
         Index Cond: ("outer".type_id = cvt.cvterm_id)
         Filter: (((name)::text <> 'gene'::text) AND ((name)::text <> 'protein'::text) AND ((name)::text <>
'natural_transposable_element'::text)AND ((name)::text <> 'chromosome_structure_variation'::text) AND ((name)::text <>
'chromosome_arm'::text)AND ((name)::text <> 'repeat_region'::text)) 
 Total runtime: 2463.968 ms
(13 rows)


=======

I tried tuning configs, including shutting off enable seqscan, forcing use of index (set shared_buffers high
with random_page_cost set low).  A colleague who gets 1697ms on Pg8.1.4 with this query provided his
postgresql.conf -- didn't help....

We use standard dump/load commands between these servers:
        pg_dump -O fb_2007_01_05 | compress > fb_2007_01_05.Z
        uncompress -c fb_2007_01_05.Z  |  psql fb_2007_01_05

Hardware/OS specs:
    - Pg7.3.2:  SunFire 280R, 900mHz SPARC processor, 3gb total RAM, 10Krpm SCSI internal disks, Solaris 2.8
    - Pg8.1.4:  v240 - dual Ultra-SPARC IIIi 1500MHz SPARC processor, 8GB total RAM, Solaris 2.10
      (used both Sun-supplied postgres binaries, and compiled postgres from source)


Thanks for your help,
Susan Russo