Skip site navigation (1) Skip section navigation (2)

Re: partial indexes not used on parameterized queries?

From: Dirk Lutzebäck <lutzeb(at)aeccom(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, Sven Geisler <sgeisler(at)aeccom(dot)com>
Subject: Re: partial indexes not used on parameterized queries?
Date: 2006-07-10 11:35:40
Message-ID: 44B23B8C.2080007@aeccom.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi Simon,

are you sure this has not been changed? I'm pretty sure my code worked a 
while ago. We are using Perl DBD::Pg on the client side and almost never 
pass parameters inside the SQL string for security reasons. I can't say 
if it broke from 8.0 -> 8.1 for us or in one of the minor 8.1 releases.

In any case I would see this as a security problem because you cannot 
control sql code injection easily (as with using DBD::Pg) if you have to 
pass parameters in the SQL string to use partial indexes.

Regards,

Dirk

Simon Riggs wrote:
> On Mon, 2006-07-10 at 12:22 +0200, Dirk Lutzebäck wrote:
>
>   
>> 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.
>>     
>
>   
>> Any clues? Has this behaviour changed for a while?
>>     
>
> 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.
>
>   

-- 
/This email and any files transmitted with it are confidential and 
intended solely for the use of the individual or entity to whom they are 
addressed. If you are not the intended recipient, you should not copy 
it, re-transmit it, use it or disclose its contents, but should return 
it to the sender immediately and delete your copy from your system. 
Thank you for your cooperation./

*Dirk Lutzebäck* <lutzeb(at)aeccom(dot)com> Tel +49.30.5362.1635 Fax .1638
CTO AEC/communications GmbH <http://www.aeccom.com>, Berlin, Germany

In response to

Responses

pgsql-bugs by date

Next:From: Christian KratzerDate: 2006-07-10 12:00:30
Subject: Re: partial indexes not used on parameterized queries?
Previous:From: Simon RiggsDate: 2006-07-10 11:19:25
Subject: Re: partial indexes not used on parameterized queries?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group