Re: Weird performance issue with custom function with a for loop.

Nicos Panayides <nicos(at)magneta(dot)com(dot)cy> 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