Hi,<br /><br /> we are using postgresql 8.1.4 and found that partial indexes are <b>not</b> being used when the query
isrun using parameters, eg. in a function. When running the same query with arguments passed in the query string
partialindexes are being used.<br /><br /><b>here is the index:</b><br /><br /> CREATE INDEX c_6012_index ON
consumption(voi) WHERE code = 6012 AND val1 IS NULL;<br /><br /><b>here is the query using parameters inside the query
stringwhich uses an index scan in turn:</b><br /><br /> explain analyze UPDATE c SET val1=1784 WHERE code=6012 AND
voi='1923328-8-0-0'AND val1 IS NULL;<br /> QUERY PLAN<br />
-------------------------------------------------------------------------------------------------------------------------------<br
/> 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)<br
/> Index Cond: (voi = '1923328-8-0-0'::text)<br /> Filter: ((code = 6012) AND (val1 IS NULL))<br /> Total
runtime:0.304 ms<br /> (4 rows)<br /><br /><br /><b>Now put it in a function with parameter passing we get a seq
scan:</b><br/><br /><br /> CREATE FUNCTION setsize(integer, integer, text)<br /> RETURNS integer AS<br /> $BODY$<br
/>DECLARE<br /> v_size alias for $1;<br /> v_code alias for $2;<br /> v_voi alias for
$3;<br/> r record;<br /> BEGIN<br /> FOR r IN<br /> EXPLAIN UPDATE c SET val1=v_size
WHEREcode=v_code AND voi=v_voi AND val1 IS NULL LOOP<br /> RAISE NOTICE '%', r;<br /> END LOOP;<br />
RETURN 0;<br /> END;<br /><br /> # select setsize(1784, 6012, '1923328-8-0-0');<br /> NOTICE: ("Seq Scan on c
(cost=0.00..344372.82rows=1 width=164)")<br /> NOTICE: (" Filter: ((code = $2) AND (voi = $3) AND (val1 IS
NULL))")<br/> setsize<br /> ---------<br /> 0<br /> (1 row)<br /><br /><br /> Bummer, a sequential scan is
beingrun.<br /><br /> Any clues? Has this behaviour changed for a while?<br /><br /> Regards,<br /><br /> Dirk<br /><br
/><br/>
