Re: New array functions

> See:
> http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154
>
> regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM pg_shadow
> s, pg_group g WHERE s.usesysid = any (g.grolist);

These forms below are all equivalent, right?

If so ideally they would all be converted to an equivalent form and therefore
produce the same plan. I guess I'm wishing for a pony though. But I think
currently I'm stuck with the worst of these and I don't see any way of
escaping to the better plans.

Incidentally, "HashAggregate"?! Based on the earlier discussion on this I
would have expected that line to read "Materialize"

slo=> explain select * from store_location where store_location_id in (1,2,3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using store_location_pkey, store_location_pkey, store_location_pkey on store_location (cost=0.00..17.74 rows=3 width=523)
Index Cond: ((store_location_id = 1) OR (store_location_id = 2) OR (store_location_id = 3))
(2 rows)

slo=> explain select * from store_location where store_location_id in (select 1 union all select 2 union all select 3);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop (cost=0.10..17.86 rows=3 width=523)
-> HashAggregate (cost=0.10..0.10 rows=3 width=4)
-> Subquery Scan "IN_subquery" (cost=0.00..0.09 rows=3 width=4)
-> Append (cost=0.00..0.06 rows=3 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Index Scan using store_location_pkey on store_location (cost=0.00..5.91 rows=1 width=523)
Index Cond: (store_location.store_location_id = "outer"."?column?")
(12 rows)

slo=> explain select * from store_location where store_location_id = any (array[1,2,3]);
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on store_location (cost=0.00..825.75 rows=5954 width=523)
Filter: (store_location_id = ANY ('{1,2,3}'::integer[]))
(2 rows)

--
greg