From: Chuck D. <pgsql-performance@nullmx.com>
To: pgsql-performance@postgresql.org
Subject: Re: Rewriting DISTINCT and losing performance
Date: Mon, 21 May 2007 12:17:44 -0600
Message-ID: <200705211217.45205.pgsql-performance@nullmx.com> (raw)
In-Reply-To: <4651D813.5050100@archonet.com>
References: <200705202228.32129.pgsql-performance@nullmx.com>
<200705210909.44335.pgsql-performance@nullmx.com>
<4651D813.5050100@archonet.com>
On Monday 21 May 2007 11:34, Richard Huxton wrote:
> Chuck D. wrote:
>
> 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"?
Well here is where it gets strange. The CLUSTER was just one thing I tried to
do to enhance the performance. I had the same result prior to cluster.
However, after updating that country_id column to NOT NULL and eliminating
NULL values it will use the country_id index and perform quickly. Oddly
enough, the original table, world_city still has NULL values in the
country_id column and it has always used the country_id index.
Doesn't that seem a bit strange? Does it have to do with the smaller size of
the new table maybe?
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: <200705211217.45205.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