Re: annoying query/planner choice

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 <ronz@ravensfield.com> 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