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