From: David Scott <davids@apptechsys.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance hit on large row counts
Date: Mon, 26 Dec 2005 15:07:23 -0800
Message-ID: <43B077AB.1000309@apptechsys.com> (raw)
In-Reply-To: <12356.1135632990@sss.pgh.pa.us>
References: <43B05AA6.9020902@apptechsys.com>
<12356.1135632990@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: davids@apptechsys.com, tgl@sss.pgh.pa.us
Subject: Re: Performance hit on large row counts
In-Reply-To: <43B077AB.1000309@apptechsys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox