Nicos Panayides <nicos@xxxxxxxxxxxxxx> writes: > On 01/31/2011 08:48 PM, Tom Lane wrote: >> It seems likely that you're getting a different plan for the generic >> case because that user id isn't representative of the overall average >> for the column. > I tried the prepared statement with both $1 and 1811 for user_id and > here's the plans I got: [ bad ] > " -> Seq Scan on game_round_actions (cost=0.00..51702078.26 > rows=314 width=53)" > " Filter: ((action_time >= $2) AND (action_time <= $3) AND > (sub_action_id = 0) AND (user_id = $1))" [ good ] > " -> Index Scan using i_session on game_round_actions > (cost=0.00..224166.97 rows=300 width=53)" > " Index Cond: ((action_time >= $2) AND (action_time <= $3))" > " Filter: (user_id = 1811)" So the question is why it won't use that index in the parameterized case ... > CREATE INDEX i_session > ON game_round_actions > USING btree > (action_time) > WHERE user_id <> 0 AND sub_action_id = 0; ... and the answer is that it can't prove user_id <> 0 when it doesn't know the value of the parameter equated to user_id, so it cannot build a plan that relies on using that partial index. (IOW, if it did use the index, it would get the wrong answer if $1 happened to be zero.) I don't know the reason you had for making the index partial in the first place, but maybe you should reconsider that. Another possibility is to explicitly include "user_id <> 0" in the query conditions, if you're certain that the passed-in value is never zero. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
