Horton

Thread

  1. partial indexes not used on parameterized queries?

    Dirk Lutzebäck <lutzeb@aeccom.com> — 2006-07-10T10:22:22Z

    Hi,

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.

*here is the index:*

CREATE INDEX c_6012_index ON consumption (voi) WHERE code = 6012 AND 
val1 IS NULL;

*here is the query using parameters inside the query string which uses 
an index scan in turn:*

explain analyze UPDATE c SET val1=1784 WHERE code=6012 AND 
voi='1923328-8-0-0' AND val1 IS NULL;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using c_6012_index on c  (cost=0.00..4.71 rows=1 width=164) 
(actual time=0.196..0.196 rows=0 loops=1)
   Index Cond: (voi = '1923328-8-0-0'::text)
   Filter: ((code = 6012) AND (val1 IS NULL))
 Total runtime: 0.304 ms
(4 rows)


*Now put it in a function with parameter passing we get a seq scan:*


CREATE FUNCTION setsize(integer, integer, text)
  RETURNS integer AS
$BODY$
DECLARE
        v_size    alias for $1;
        v_code    alias for $2;
        v_voi     alias for $3;
        r       record;
BEGIN
        FOR r IN
        EXPLAIN UPDATE c SET val1=v_size WHERE code=v_code AND voi=v_voi 
AND val1 IS NULL  LOOP
        RAISE NOTICE '%', r;
        END LOOP;
        RETURN 0;
END;

# select setsize(1784, 6012, '1923328-8-0-0');
NOTICE:  ("Seq Scan on c  (cost=0.00..344372.82 rows=1 width=164)")
NOTICE:  ("  Filter: ((code = $2) AND (voi = $3) AND (val1 IS NULL))")
 setsize
---------
       0
(1 row)


Bummer, a sequential scan is being run.

Any clues? Has this behaviour changed for a while?

Regards,

Dirk

  2. Re: partial indexes not used on parameterized queries?

    Simon Riggs <simon@2ndquadrant.com> — 2006-07-10T11:19:25Z

    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.

-- 
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com

  3. Re: partial indexes not used on parameterized queries?

    Dirk Lutzebäck <lutzeb@aeccom.com> — 2006-07-10T11:35:40Z

    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@aeccom.com> Tel +49.30.5362.1635 Fax .1638
CTO AEC/communications GmbH <http://www.aeccom.com>, Berlin, Germany

  4. Re: partial indexes not used on parameterized queries?

    Christian Kratzer <ck@cksoft.de> — 2006-07-10T12:00:30Z

    Hi,

On Mon, 10 Jul 2006, [UTF-8] Dirk Lutzebäck wrote:

> 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.

DBD::Pg only recently started preparing queries in the server.

Older versions of DBD::Pg simulated prepared statements behind the
scenes so your postgresql server got to plan every query individually.

You might want to switch of server side prepares for your specific 
query and see if that helps.  See the description of the pg_server_prepare
in the DBD::Pg manpage on how to go about this.

> 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.

I hope you are not relying on prepared statements as your only
defense against sql code injection.

Greetings
Christian

-- 
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136

  5. Re: partial indexes not used on parameterized queries?

    Simon Riggs <simon@2ndquadrant.com> — 2006-07-10T12:29:37Z

    On Mon, 2006-07-10 at 13:35 +0200, Dirk Lutzebäck wrote:

> 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.

That's not what I said. I see no security problem as a result of this
behaviour.

-- 
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com

  6. Re: partial indexes not used on parameterized queries?

    Dirk Lutzebäck <lutzeb@aeccom.com> — 2006-07-10T13:28:13Z

    Ok, we checked our client code to eliminate this problem. Thanks for the 
doc patch.

Regards,

Dirk

Simon Riggs wrote:
> On Mon, 2006-07-10 at 13:35 +0200, Dirk Lutzebäck wrote:
>
>   
>> 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.
>>     
>
> That's not what I said. I see no security problem as a result of this
> behaviour.
>
>   

-- 
/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@aeccom.com> Tel +49.30.5362.1635 Fax .1638
CTO AEC/communications GmbH <http://www.aeccom.com>, Berlin, Germany