I have a simple query which uses 32ms on 7.4.14 and 1015ms on 8.2.0. I guess 7.4.14 creates a better execution plan than 8.2.0 for this query but i don't know how to get it to select a better one. Explain analyse output will be found near the end of the e-mail.Explain analyze is run several times to get a stable result so i guess the numbers presented is with as much as possible data in memory buffers. Query: "select * from view_subset;" run against 7.4.14 server. QUERY PLAN ------------------------------------------------------------------------ Nested Loop (cost=0.00..1400.86 rows=17 width=8) (actual time=0.161..26.287 rows=68 loops=1) -> Index Scan using uut_result_subset_start_date_time_idx on uut_result_subset ur (cost=0.00..63.28 rows=18 width=4) (actual time=0.052..0.195 rows=68 loops=1) Index Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) -> Index Scan using step_result_uut_result_idx on step_result_subset sr (cost=0.00..74.28 rows=2 width=8) (actual time=0.149..0.379 rows=1 loops=68) Index Cond: ("outer".id = sr.uut_result) Filter: (step_parent = 0) Total runtime: 26.379 ms Query: "select * from view_subset;" run against 8.4.0 server. QUERY PLAN ---------------------------------------------------------------------- Hash Join (cost=339.61..77103.61 rows=96 width=8) (actual time=5.249..1010.669 rows=68 loops=1) Hash Cond: (sr.uut_result = ur.id) -> Index Scan using step_result_subset_parent_key on step_result_subset sr (cost=0.00..76047.23 rows=143163 width=8) (actual time=0.082..905.326 rows=176449 loops=1) Index Cond: (step_parent = 0) -> Hash (cost=339.31..339.31 rows=118 width=4) (actual time=0.149..0.149 rows=68 loops=1) -> Bitmap Heap Scan on uut_result_subset ur (cost=4.90..339.31 rows=118 width=4) (actual time=0.060..0.099 rows=68 loops=1) Recheck Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) -> Bitmap Index Scan on uut_result_subset_start_date_time_idx (cost=0.00..4.90 rows=118 width=0) (actual time=0.050..0.050 rows=68 loops=1) Index Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) Total runtime: 1010.775 ms
Did you lower random_page_cost in 8.2 (which defaults to 4.0)? If not try 2. regards Claus ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
