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)