Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 395359FB478 for ; Mon, 21 May 2007 14:34:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 10751-01 for ; Mon, 21 May 2007 14:34:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 57AB99FB4E9 for ; Mon, 21 May 2007 14:34:13 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id B991F75065; Mon, 21 May 2007 18:34:11 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id ABB7D274115; Mon, 21 May 2007 18:34:11 +0100 (BST) Message-ID: <4651D813.5050100@archonet.com> Date: Mon, 21 May 2007 18:34:11 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: pgsql-performance@nullmx.com Cc: pgsql-performance@postgresql.org Subject: Re: Rewriting DISTINCT and losing performance References: <200705202228.32129.pgsql-performance@nullmx.com> <465162E6.4030205@agliodbs.com> <200705210909.44335.pgsql-performance@nullmx.com> In-Reply-To: <200705210909.44335.pgsql-performance@nullmx.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/395 X-Sequence-Number: 24785 Chuck D. wrote: > 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) The only thing I can think of is that the CLUSTERing on city.country_id makes the system think it'll be cheaper to seq-scan the whole table. I take it you have got 2 million rows in "city"? -- Richard Huxton Archonet Ltd