"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> BTW, are you aware that those two things seem to be returning
> different numbers of rows from each table and consequently from the
> whole query?
What's even stranger is that the whole-table seqscan on hostsqueue is
apparently running slower than the whole-table indexscan:
> -> Seq Scan on hostsqueue hq (cost=0.00..75243.51 rows=35351 width=16) (actual time=518.88..122024.50
rows=28610loops=1)
> -> Index Scan using hostsqueue_pkey on hostsqueue hq (cost=0.00..137603.50 rows=35351 width=16) (actual
time=34.90..90831.97rows=28623 loops=1)
Now that just ain't physically possible, given comparable starting
conditions: an indexscan has to do more work, both CPU and I/O.
I suspect that the indexscan test was run second and benefited from most
of the table having been brought into RAM during the seqscan. If so, a
repeat test will show different results.
In any case, it'd seem that the planner is not making any major errors
here: its estimates were not that far out of line from reality.
It did pick the slightly slower plan, but the difference is well within
what I'd call reasonable estimation error.
regards, tom lane