Re: partial indexes not used on parameterized queries? - Mailing list pgsql-bugs

Hi Simon,<br /><br /> are you sure this has not been changed? I'm pretty sure my code worked a while ago. We are using
PerlDBD::Pg on the client side and almost never pass parameters inside the SQL string for security reasons. I can't say
ifit broke from 8.0 -> 8.1 for us or in one of the minor 8.1 releases.<br /><br /> In any case I would see this as a
securityproblem because you cannot control sql code injection easily (as with using DBD::Pg) if you have to pass
parametersin the SQL string to use partial indexes.<br /><br /> Regards,<br /><br /> Dirk<br /><br /> Simon Riggs
wrote:<blockquote cite="mid1152530366.2518.20.camel@localhost.localdomain" type="cite"><pre wrap="">On Mon, 2006-07-10
at12:22 +0200, Dirk Lutzebäck wrote: 
 </pre><blockquote type="cite"><pre wrap="">we are using postgresql 8.1.4 and found that partial indexes are not
being used when the query is run using parameters, eg. in a function.
When running the same query with arguments passed in the query string
partial indexes are being used.   </pre></blockquote><pre wrap=""> </pre><blockquote type="cite"><pre wrap="">Any
clues?Has this behaviour changed for a while?   </pre></blockquote><pre wrap=""> 
No, it's always worked like this.

The index can only be used if we know at plan time that the index
predicate is true for all times that the query is executed. We cannot
know this for the exact query and index combination you have requested.
If we acted differently, your query would return the wrong answer in
some circumstances.

I can't find anything in the manual that explains this distinction.

Here's an example that explains this more easily:

If your index looked like this

CREATE INDEX c_6000_index ON consumption (voi)
WHERE    code > 5000
AND val1 IS NULL;

and your query like this

UPDATE c
SET val1=1784
WHERE
(   code > 6000
AND val1 IS NULL )
AND code = ?
AND voi = '1923328-8-0-0';

...then the index could be used, because the index predicate is implied
by part of the query clause for all values of the parameter.

So its best to look for some other static definition of the index.

I'll submit a doc patch.
 </pre></blockquote><br /><div class="moz-signature">-- <br /><small><i>This email and any files transmitted with it
areconfidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not
theintended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to
thesender immediately and delete your copy from your system. Thank you for your cooperation.</i></small><p><b>Dirk
Lutzebäck</b><a class="moz-txt-link-rfc2396E" href="mailto:lutzeb@aeccom.com"><lutzeb@aeccom.com></a> Tel
+49.30.5362.1635Fax .1638<br /> CTO <a href="http://www.aeccom.com">AEC/communications GmbH</a>, Berlin, Germany </div>