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

Re: partial indexes not used on parameterized queries?

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Dirk Lutzebäck <lutzeb(at)aeccom(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:19:25
Message-ID: 1152530366.2518.20.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugs
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


In response to

Responses

pgsql-bugs by date

Next:From: Dirk LutzebäckDate: 2006-07-10 11:35:40
Subject: Re: partial indexes not used on parameterized queries?
Previous:From: SudhakarDate: 2006-07-10 11:15:59
Subject: BUG #2522: while executing pgdump - error occurs

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