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@xxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
