Message-ID: <43B077AB.1000309@apptechsys.com>
Date: Mon, 26 Dec 2005 15:07:23 -0800
From: David Scott <davids@apptechsys.com>
User-Agent: Mozilla Thunderbird 1.0.2-6 (X11/20050513)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance hit on large row counts
References: <43B05AA6.9020902@apptechsys.com> <12356.1135632990@sss.pgh.pa.us>
In-Reply-To: <12356.1135632990@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

>The CLUSTER may be affecting things in some other way, like by squeezing out dead tuples causing a
>reduction in the total table and index sizes.
>  
>
    I didn't mention I was the only user with transactions open on the 
system during this.  Would cluster eliminate more rows then vacuum full 
if the only open transaction is the one running the vacuum and it is a 
clean transaction?

>You should try increasing the statistics targets on the columns you use
>in the WHERE conditions.
>  
>
    We set it to 500 and couldn't get it to repeat the plan where it was 
using the pair_idx, so that certainly helps.

>I'm not at all sure I believe your premise that querying for a different
>key value excludes cache effects, btw.  On modern hardware it's likely
>that CLUSTER would leave the *whole* of these tables sitting in kernel
>disk cache.
>  
>
    You are exactly right.  After rebooting the entire box and running 
the query the query time was 15 seconds.  Rebooting the box, running 
cluster on all three tables and then executing the query was 120 ms.  Is 
calling cluster the only way to ensure that these tables get loaded into 
cache?  Running select * appeared to cache some but not all.

    Thanks