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

>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=1 loops=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.008 rows=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.041 rows=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.037 rows=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=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'::character varying) 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)