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
