[BUGS] partial indexes not used on parameterized queries?

[Dirk Lutzebäck]

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