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

Re: Performance hit on large row counts

From: David Scott <davids(at)apptechsys(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance hit on large row counts
Date: 2005-12-26 23:07:23
Message-ID: 43B077AB.1000309@apptechsys.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: David LangDate: 2005-12-26 23:27:18
Subject: Re: What's the best hardver for PostgreSQL 8.1?
Previous:From: Alex TurnerDate: 2005-12-26 23:04:40
Subject: Re: What's the best hardver for PostgreSQL 8.1?

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