From: Mark Lewis <mark.lewis@mir3.com>
To: pgsql-performance@lusis.org
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: Thu, 15 Jun 2006 12:01:03 -0700
Message-ID: <1150398064.31200.76.camel@archimedes> (raw)
In-Reply-To: <c841561b0606151146j5f8410f2qaeaff7649b70cd20@mail.gmail.com>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
<1150396425.31200.66.camel@archimedes>
<c841561b0606151146j5f8410f2qaeaff7649b70cd20@mail.gmail.com>
On Thu, 2006-06-15 at 14:46 -0400, John Vincent wrote:
> One question that we came up with is how does this affect other
> aggregate functions like MAX,MIN,SUM and whatnot? Being that this is
> our data warehouse, we use these all the time. As I've said
> previously, I didn't know a human could generate some of the queries
> we've passed through this system.
Previously, MIN and MAX would also run slowly, for the same reason as
COUNT(*). But there really isn't a need for that, since you can still
get a big speedup by scanning the index in order, looking up each row
and stopping as soon as you find a visible one.
This has been fixed so newer versions of PG will run quickly and use the
index for MIN and MAX. I don't remember which version had that change;
it might not be until 8.2. You can dig the archives to find out for
sure.
For older versions of PG before the fix, you can make MIN and MAX run
quickly by rewriting them in the following form:
SELECT column FROM table ORDER BY column LIMIT 1;
Unfortunately SUM is in the same boat as COUNT; in order for it to
return a meaningful result it must inspect visibility information for
all of the rows.
-- Mark
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: mark.lewis@mir3.com, pgsql-performance@lusis.org
Subject: Re: Optimizer internals
In-Reply-To: <1150398064.31200.76.camel@archimedes>
* 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