From: Chuck D. <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
Message-ID: <200705210909.44335.pgsql-performance@nullmx.com> (raw)
In-Reply-To: <465162E6.4030205@agliodbs.com>
References: <200705202228.32129.pgsql-performance@nullmx.com>
<465162E6.4030205@agliodbs.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql-performance@nullmx.com
Subject: Re: Rewriting DISTINCT and losing performance
In-Reply-To: <200705210909.44335.pgsql-performance@nullmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox