Re: Rewriting DISTINCT and losing performance

On Monday 21 May 2007 03:14, Josh Berkus wrote:
> Chuck,
>
> Can we see the plan?
>
> --Josh
>

Sorry Josh,  I guess I could have just used EXPLAIN instead of EXPLAIN
ANALYZE.

# explain
SELECT   country_id, country_name
FROM     geo.country
WHERE country_id IN
 (select country_id FROM geo.city)
;
                             QUERY PLAN
--------------------------------------------------------------------
 Nested Loop IN Join  (cost=0.00..1252.60 rows=155 width=15)
   Join Filter: (country.country_id = city.country_id)
   ->  Seq Scan on country  (cost=0.00..6.44 rows=244 width=15)
   ->  Seq Scan on city  (cost=0.00..43409.12 rows=2122712 width=2)
(4 rows)


Versus the same query using the older, larger world_city table:

# explain
SELECT   country_id, country_name
FROM     geo.country
WHERE country_id IN
 (select country_id FROM geo.world_city)
;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Nested Loop IN Join  (cost=0.00..23.16 rows=155 width=15)
   ->  Seq Scan on country  (cost=0.00..6.44 rows=244 width=15)
   ->  Index Scan using idx_world_city_country_id on world_city
(cost=0.00..706.24 rows=12602 width=2)
         Index Cond: (country.country_id = world_city.country_id)
(4 rows)