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