From: Tom Lane <tgl@sss.pgh.pa.us>
To: henk de wit <henk53602@hotmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: How to speed up min/max(id) in 50M rows table?
Date: Fri, 12 Oct 2007 18:17:41 -0400
Message-ID: <5937.1192227461@sss.pgh.pa.us> (raw)
In-Reply-To: <5754.1192226626@sss.pgh.pa.us>
References: <BAY124-W503931258D0AE13AF37546F5A00@phx.gbl>
<5754.1192226626@sss.pgh.pa.us>
I wrote:
> The only way I can see for that to be so slow is if you have a very
> large number of rows where payment_id is null --- is that the case?
> There's not a lot you could do about that in existing releases :-(.
Actually, there is a possibility if you are willing to change the query:
make a partial index that excludes nulls. Toy example:
regression=# create table fooey(f1 int);
CREATE TABLE
regression=# create index fooeyi on fooey(f1) where f1 is not null;
CREATE INDEX
regression=# explain select max(f1) from fooey;
QUERY PLAN
---------------------------------------------------------------
Aggregate (cost=36.75..36.76 rows=1 width=4)
-> Seq Scan on fooey (cost=0.00..31.40 rows=2140 width=4)
(2 rows)
regression=# explain select max(f1) from fooey where f1 is not null;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Result (cost=0.03..0.04 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.03 rows=1 width=4)
-> Index Scan Backward using fooeyi on fooey (cost=0.00..65.55 rows=2129 width=4)
Filter: (f1 IS NOT NULL)
(5 rows)
Probably the planner ought to be smart enough to figure this out without
the explicit WHERE in the query, but right now it isn't.
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, henk53602@hotmail.com
Subject: Re: How to speed up min/max(id) in 50M rows table?
In-Reply-To: <5937.1192227461@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