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? 
In-reply-to: <5754.1192226626@sss.pgh.pa.us> 
References: <BAY124-W503931258D0AE13AF37546F5A00@phx.gbl>
	<5754.1192226626@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 12 Oct 2007 18:03:46 -0400"
Date: Fri, 12 Oct 2007 18:17:41 -0400
Message-ID: <5937.1192227461@sss.pgh.pa.us>
From: Tom Lane <tgl@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