Skip site navigation (1) Skip section navigation (2)

Re: annoying query/planner choice

From: Andrew Rawnsley <ronz(at)ravensfield(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: annoying query/planner choice
Date: 2004-01-12 15:02:09
Message-ID: 4B2B3053-4510-11D8-BF17-000393A47FCC@ravensfield.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)ravensfield(dot)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


In response to

pgsql-performance by date

Next:From: David ShadovitzDate: 2004-01-12 15:37:36
Subject: Re: COUNT & Pagination
Previous:From: Richard HuxtonDate: 2004-01-12 10:16:37
Subject: Re: COUNT & Pagination

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group