"Edin Kadribasic" <[EMAIL PROTECTED]> writes: > I have a query that is giving the optimizer (and me) great headache.
The main problem seems to be that the rowcount estimates for axp_temp_order_match and axp_dayschedule are way off: > -> Index Scan using axp_temp_order_match_idx1 on > axp_temp_order_match a (cost=0.00..209.65 rows=426 width=4) (actual > time=0.277..0.512 rows=6 loops=1) > Index Cond: (sid = 16072) > -> Index Scan using axp_dayschedule_day_idx on > axp_dayschedule ds (cost=0.00..3.02 rows=1 width=8) (actual > time=0.036..3.973 rows=610 loops=1) > Index Cond: (("day" >= '2005-05-12'::date) > AND ("day" <= '2005-05-12'::date)) > Filter: (used = B'1'::"bit") > -> Index Scan using axp_temp_order_match_idx1 on > axp_temp_order_match a (cost=0.00..2.45 rows=1 width=4) (actual > time=0.027..2.980 rows=471 loops=1) > Index Cond: (sid = 16092) > -> Index Scan using axp_dayschedule_day_idx on > axp_dayschedule ds (cost=0.00..3.02 rows=1 width=8) (actual > time=0.015..3.557 rows=606 loops=471) > Index Cond: (("day" >= '2005-05-13'::date) AND > ("day" <= '2005-05-13'::date)) > Filter: (used = B'1'::"bit") Do you ANALYZE these tables on a regular basis? If so, it may be necessary to increase the statistics target to the point where you get better estimates. > Please note that sometimes when I get "bad plan" in the logfile, I just > re-run the query and the optimizer chooses the more efficient one. That's fairly hard to believe, unless you've got autovacuum running in the background. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq