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

partial indexes not used on parameterized queries?

From: Dirk Lutzebäck <lutzeb(at)aeccom(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: Sven Geisler <sgeisler(at)aeccom(dot)com>
Subject: partial indexes not used on parameterized queries?
Date: 2006-07-10 10:22:22
Message-ID: 44B22A5E.3050800@aeccom.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: SudhakarDate: 2006-07-10 11:15:59
Subject: BUG #2522: while executing pgdump - error occurs
Previous:From: Michael FuhrDate: 2006-07-10 02:18:11
Subject: Re: BUG #2521: pg_restore is hanging

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