Re: REVISIT specific query (not all) on Pg8 MUCH slower than Pg7

Поиск
Список
Период
Сортировка
От Susan Russo
Тема Re: REVISIT specific query (not all) on Pg8 MUCH slower than Pg7
Дата
Msg-id 200705101408.l4AE8fFt017962@larrys.harvard.edu
обсуждение исходный текст
Ответ на REVISIT specific query (not all) on Pg8 MUCH slower than Pg7  (Susan Russo <russo@morgan.harvard.edu>)
Список pgsql-performance
>Quick reminders:
>*) Did you recreate all the indexes on the new system after the initdb?
>*) Did you vacuum and analyze after loading your data?

No, I didn't - am reindexing db now and will run vacuum analyze afterwards.

>I suggest you provide "explain analyze" output for the query on both versions.

Pg8:

-----------------------------------------------------------------
 Merge Join  (cost=151939.73..156342.67 rows=10131 width=1585) (actual time=0.129..0.129 rows=0 loops=1)
   Merge Cond: ("outer".cvterm_id = "inner".type_id)
   ->  Index Scan using cvterm_pkey on cvterm cvt  (cost=0.00..4168.22 rows=32478 width=520) (actual time=0.044..0.044
rows=1loops=1) 
         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))
   ->  Sort  (cost=151939.73..151965.83 rows=10441 width=1073) (actual time=0.079..0.079 rows=0 loops=1)
         Sort Key: f.type_id
        ->  Nested Loop  (cost=17495.27..151242.80 rows=10441 width=1073) (actual time=0.070..0.070 rows=0 loops=1)
               ->  Hash Join  (cost=17495.27..88325.38 rows=10441 width=525) (actual time=0.068..0.068 rows=0 loops=1)
                     Hash Cond: ("outer".dbxref_id = "inner".dbxref_id)
                    ->  Seq Scan on feature_dbxref fd  (cost=0.00..34182.71 rows=2088171 width=9) (actual
time=0.008..0.008rows=1 loops=1) 
                     ->  Hash  (cost=17466.34..17466.34 rows=11572 width=524) (actual time=0.042..0.042 rows=0 loops=1)
                           ->  Bitmap Heap Scan on dbxref dx  (cost=117.43..17466.34 rows=11572 width=524) (actual
time=0.041..0.041rows=0 loops=1) 
                                 Filter: ((accession)::text ~~ 'AY851043%'::text)
                                 ->  Bitmap Index Scan on dbxref_idx2  (cost=0.00..117.43 rows=11572 width=0) (actual
time=0.037..0.037rows=0 loops=1) 
                                       Index Cond: (((accession)::text >= 'AY851043'::character varying) AND
((accession)::text< 'AY851044'::character varying)) 
               ->  Index Scan using feature_pkey on feature f  (cost=0.00..6.01 rows=1 width=556) (never executed)
                     Index Cond: ("outer".feature_id = f.feature_id)
 Total runtime: 0.381 ms
(18 rows)


=======

Pg7:

-----------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..23.45 rows=1 width=120) (actual time=0.08..0.08 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..17.49 rows=1 width=82) (actual time=0.08..0.08 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..11.93 rows=1 width=30) (actual time=0.08..0.08 rows=0 loops=1)
               ->  Index Scan using dbxref_idx2 on dbxref dx  (cost=0.00..5.83 rows=1 width=21) (actual time=0.08..0.08
rows=0loops=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: 0.36 msec
(14 rows)


В списке pgsql-performance по дате отправления:

Предыдущее
От: Bill Moran
Дата:
Сообщение: Re: REVISIT specific query (not all) on Pg8 MUCH slower than Pg7
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: Nested loops overpriced