> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org