Re: Rewriting DISTINCT and losing performance

["Chuck D." <pgsql-performance(at)nullmx(dot)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?