>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)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
