Glyn Astill <glynastill@yahoo.co.uk> writes:
> Ah, retracing my steps forget that; there's no sort because it's not the same query at all.
OK, that explains why things didn't seem to add up.
> Going back to my original point though, is there any way to get the planner to choose a better plan for the original
distinctquery? Or is it just a simple no because I set enable_sort to off?
It's a bad combination. Matters will be better in 8.4, but in existing
releases the *only* way that the planner knows how to implement DISTINCT
is sort-and-unique. If you then throw in enable_sort = off, it's
reduced to finding an index that matches the required sort order and
scanning primarily on that, no matter how bad the resulting plan is.
I'd recommend using GROUP BY in preference to DISTINCT if you are going
to try to hobble along with enable_sort off.
Also, it'd be worth revisiting the question of whether you really still
need enable_sort off ... personally, I'd think that reducing
random_page_cost is a much saner way of nudging the planner in the
direction of preferring indexscans.
BTW, it might be a bit late for this, but you'd be a lot better off
performance-wise with bigint join keys instead of numeric(8,0).
Numeric is slow, and at that field width it's not buying you anything at
all.
regards, tom lane