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?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
