From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Scott <davids@apptechsys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance hit on large row counts
Date: Mon, 26 Dec 2005 16:36:30 -0500
Message-ID: <12356.1135632990@sss.pgh.pa.us> (raw)
In-Reply-To: <43B05AA6.9020902@apptechsys.com>
References: <43B05AA6.9020902@apptechsys.com>
David Scott <davids@apptechsys.com> writes:
> We are trying to ascertain if we are up against the limits of what
> postgres can accomplish without having the tables clustered. ...
> We are aware that there is a minimum time that is required to resolve
> the index values against the table to ascertain that they are live rows,
> and we believe we are circumventing that time to some extent by taking
> advantage of the rows being in physical order with the cluster. So does
> this lead us to the conclusion that the differences in the query times
> is how long is takes us to check on disk whether or not these rows are live?
Both of your initial examples are bitmap scans, which should be pretty
insensitive to index correlation effects --- certainly the planner
assumes so. What I'd want to know about is why the planner is picking
different indexes for the queries. 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.
The join examples use plain indexscans, which *would* be affected by
correlation ... but again, why are you getting a different scan plan
for "stuff" than in the non-join case?
It's not helping you that the rowcount estimates are so far off.
I think the different plans might be explained by the noise in the
rowcount estimates.
You should try increasing the statistics targets on the columns you use
in the WHERE conditions.
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, davids@apptechsys.com
Subject: Re: Performance hit on large row counts
In-Reply-To: <12356.1135632990@sss.pgh.pa.us>
* 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