Not to beat a dead horse excessively, but I think the below is a pretty good argument for index hints? I know the general optimizer wants to be highest priority (I very much agree with this), but I think there are fully legitimate cases like the below. Asking the user to rewrite the query in an unnatural way (or to change optimizer params that may work for 99% of queries) is, IMO not a good thing. Given that the postgres optimizer can never be perfect (as it will never have the perfect knowledge necessary for a perfect decision), I would request that index hints be reconsidered (for 9.0?). I know many users (myself included) are doing this in a very rudimentary way by disabling particular access types on a per session basis "set enable_seqscan=off; set enable_hashjoin=off; QUERY set enable_seqscan=on; set enable_hashjoin=on;"... I'd hack up a patch if I had the time at least =) Best regards, Patrick -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Monday, March 22, 2010 12:22 PM To: Christian Brink Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster Christian Brink <cbrink@xxxxxxxxxxxx> writes: > -> Nested Loop (cost=0.01..2416.59 rows=22477 width=4) > (actual time=0.595..2.150 rows=225 loops=1) > -> Index Scan using sysstrings_pkey on sysstrings > (cost=0.00..8.27 rows=1 width=182) (actual time=0.110..0.112 rows=1 loops=1) > Index Cond: (id = 'net/Console/Employee/Day End > Time'::text) > -> Index Scan using sales_tranzdate_index on sales s > (cost=0.01..1846.40 rows=22477 width=12) (actual time=0.454..1.687 > rows=225 loops=1) > Index Cond: ((s.tranzdate >= ('2010-02-15'::date + > (sysstrings.data)::time without time zone)) AND (s.tranzdate < > ('2010-02-16'::date + (sysstrings.data)::time without time zone))) > Filter: ((NOT void) AND (NOT suspended)) The fundamental reason why you're getting a bad plan choice is the factor-of-100 estimation error here. I'm not sure you can do a whole lot about that without rethinking the query --- in particular I would suggest trying to get rid of the non-constant range bounds. You're apparently already plugging in an external variable for the date, so maybe you could handle the time of day similarly instead of joining to sysstrings for it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
