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
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 |