Hi Tom,
We have managed to improve significantly on the speed of this query.
The way that we did this was through clustering the table based on the
domain index which significantly reduced the page reads that were
required in order to perform the query.
Also to find this we turned on log_statement_stats to see what it was doing.
This was on a table of roughly 600MB where the domains were randomly
dispersed.
Cheers
Bryce
Tom Lane wrote:
> Bryce Ewing <bryce@smx.co.nz> writes:
>
>> So it seems to me that once the index is in memory everything is fine
>> with the world, but the loading of the index into memory is horrendous.
>>
>
> So it would seem. What's the disk hardware on this machine?
>
> It's possible that part of the problem is table bloat, leading to the
> indexscan having to fetch many more pages than it would if the table
> were more compact.
>
> regards, tom lane
>
>
