Re: Rewriting DISTINCT and losing performance

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?