Re: New array functions - Mailing list pgsql-hackers

> 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
Scanusing 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.86rows=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.01rows=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.02rows=1 width=0)                          ->  Result  (cost=0.00..0.01 rows=1 width=0)  ->  Index Scan
usingstore_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]);
QUERYPLAN                              
 
---------------------------------------------------------------------Seq Scan on store_location  (cost=0.00..825.75
rows=5954width=523)  Filter: (store_location_id = ANY ('{1,2,3}'::integer[]))
 
(2 rows)




-- 
greg