Thread: pulling hair out trying to force replan

I've got some pretty big tables with partial indexes on very specific values. It seems as though no matter what I try to force a replan it won't plan to use the partial indexes because it seems to be caching a plan valid for all potential parameters. I'm using hibernate which uses prepared statements over jdbc. I've tried setting prepareThreshold=0 to no avail.

PARTIAL INDEX ON varchar X with varchar_pattern_ops where X like '12345%'

LOG:  duration: 9640.964 ms  execute S_127/C_128: select ... from table this_ ... where this_.TIME>$1 and (1<>1 or ((1<>1 or this_.X like $2)))
DETAIL:  parameters: $1 = '2007-02-02 04:56:38', $2 = '12345%'

If i take the query above and substitute manually the constants and do an explain it uses the partial indexes fine, and the query runs less than 10 ms...

Any suggestions would be most appreciated, I've been trying to solve this for a week now :(

Thanks,
Gene

> I've got some pretty big tables with partial indexes
> on very specific values. It seems as though no matter
> what I try to force a replan it won't plan to use the
> partial indexes because it seems to be caching a plan
> valid for all potential parameters. I'm using hibernate
> which uses prepared statements over jdbc. I've tried
> setting prepareThreshold=0 to no avail.

> Any suggestions would be most appreciated, I've been
> trying to solve this for a week now :(

Not sure how much this will help you, but you can query
The pg_prepared_statements view to find the prepared
statement that's causing your headaches (S_127/C_128
in your example) and feed it to DEALLOCATE.


http://www.postgresql.org/docs/8.2/interactive/view-pg-prepared-statemen
ts.html

http://www.postgresql.org/docs/8.2/interactive/sql-deallocate.html