pulling hair out trying to force replan

From: Gene <genekhart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pulling hair out trying to force replan
Date: 2007-02-26 05:05:06
Message-ID: 430d92a20702252105h5eeec3ddyc4a0ae788d0c61e1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2007-02-26 05:51:47 Re: pulling hair out trying to force replan
Previous Message Kenneth Downs 2007-02-26 00:21:45 Re: General Ledger db design