Probably my best solution is to find a better way to produce the information, or cache it on the
application side, as it doesn't actually change that much across client sessions.
Clustering it occurred to me - it would have to be done on a frequent basis, as the contents
of the table change constantly. What I am getting out of it with this operation doesn't change
much, so caching in a separate table, in the application layer, or both would probably shortcut
the whole problem.
Always amazing what occurs to you when you sleep on it...if only I could take a good nap in the
middle of the afternoon I would have no problems at all.
On Jan 12, 2004, at 12:40 AM, Tom Lane wrote:
Andrew Rawnsley <[EMAIL PROTECTED]> writes:--------------------I have a situation that is giving me small fits, and would like to see if anyone can shed any light on it.
In general, pulling 10% of a table *should* be faster as a seqscan than
an indexscan, except under the most extreme assumptions about clustering
(is the table clustered on site_id, by any chance?). What I suspect is
that the table is a bit larger than your available RAM, so that a
seqscan ends up flushing all of the kernel's cache and forcing a lot of
I/O, whereas an indexscan avoids the cache flush by not touching (quite)
all of the table. The trouble with this is that the index only looks
that good under test conditions, ie, when you repeat it just after an
identical query that pulled all of the needed pages into RAM. Under
realistic load conditions where different site_ids are being hit, the
indexscan is not going to be as good as you think, because it will incur
substantial I/O.
You should try setting up a realistic test load hitting different random
site_ids, and see whether it's really a win to force seqscan off for
this query or not.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
