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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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