From: "Chuck D." <pgsql-performance@nullmx.com>
Reply-To: pgsql-performance@nullmx.com
To: pgsql-performance@postgresql.org
Subject: Re: Rewriting DISTINCT and losing performance
Date: Mon, 21 May 2007 09:09:43 -0600
User-Agent: KMail/1.9.5
References: <200705202228.32129.pgsql-performance@nullmx.com>
	<465162E6.4030205@agliodbs.com>
In-Reply-To: <465162E6.4030205@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200705210909.44335.pgsql-performance@nullmx.com>

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)