BUG #14173: Not using partitions with ANY(ARRAY[...])

furstenheim@gmail.com writes:

I'm querying a table partitioned wrt to some variable, say mvar. If I do:
explain select * from mtable where mvar = 'a' or mvar = 'b'
then the query planner goes only into the two subtables related to 'a' and
'b'.
The same works with the static in
select * from mtable where mvar IN ('a', 'b')
However, it does not work if I use array
select * from mtable where mvar = ANY(ARRAY['a','b'])
the query planner schedules all subtables of the partition.

Works for me:

regression=# create table mtable (mvar int);
CREATE TABLE
regression=# create table c1 (check (mvar > 0 and mvar <= 10)) inherits(mtable);
CREATE TABLE
regression=# create table c2 (check (mvar > 10 and mvar <= 20)) inherits(mtable);
CREATE TABLE
regression=# explain select * from mtable where mvar = 3 or mvar = 4;
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..48.25 rows=26 width=4)
-> Seq Scan on mtable (cost=0.00..0.00 rows=1 width=4)
Filter: ((mvar = 3) OR (mvar = 4))
-> Seq Scan on c1 (cost=0.00..48.25 rows=25 width=4)
Filter: ((mvar = 3) OR (mvar = 4))
(5 rows)

regression=# explain select * from mtable where mvar in (3, 4);
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..41.88 rows=27 width=4)
-> Seq Scan on mtable (cost=0.00..0.00 rows=1 width=4)
Filter: (mvar = ANY ('{3,4}'::integer[]))
-> Seq Scan on c1 (cost=0.00..41.88 rows=26 width=4)
Filter: (mvar = ANY ('{3,4}'::integer[]))
(5 rows)

regression=# explain select * from mtable where mvar = any (array[3,4]);
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..41.88 rows=27 width=4)
-> Seq Scan on mtable (cost=0.00..0.00 rows=1 width=4)
Filter: (mvar = ANY ('{3,4}'::integer[]))
-> Seq Scan on c1 (cost=0.00..41.88 rows=26 width=4)
Filter: (mvar = ANY ('{3,4}'::integer[]))
(5 rows)

regression=# explain select * from mtable where mvar = any (array[13,14]);
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..41.88 rows=27 width=4)
-> Seq Scan on mtable (cost=0.00..0.00 rows=1 width=4)
Filter: (mvar = ANY ('{13,14}'::integer[]))
-> Seq Scan on c2 (cost=0.00..41.88 rows=26 width=4)
Filter: (mvar = ANY ('{13,14}'::integer[]))
(5 rows)

I speculate that you've got some sort of datatype mismatch problem, but
without seeing an exact example it's hard to diagnose.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs