As far as I can tell from EXPLAIN, there isn't any optimization done
currently on queries involving the min or max of an indexed field.
What I'm interested in is predecessor/successor queries, eg, "find
the largest value less than X".  In SQL this becomes

	SELECT max(field1) FROM table WHERE field1 < X

(for a constant X).  Currently Postgres always seems to read all the
table records with field1 < X to execute this query.

Now, if field1 has a btree index then it should be possible to answer
this query with just a probe into the index, never reading any table
entries at all.  But that implies understanding the semantics of max()
and its relationship to the ordering used by the index, so I can see
that teaching Postgres to do this in a type-independent way might be
painful.

For now, I can live with scanning all the table entries, but it would be
nice to know that someone is working on this and it'll be there by the
time my tables get huge ;-).  I see something about
	* Use indexes in ORDER BY, min(), max()(Costin Oproiu)
in the TODO list, but is this actively being worked on, and will it
solve my problem or just handle simpler cases?

Alternatively, is there a better way to do predecessor/successor
queries in SQL?

			regards, tom lane

Mon, 13 Jul 1998 16:53:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Anyone working on optimizing subset min/max queries?
> For now, I can live with scanning all the table entries, but it would be
> nice to know that someone is working on this and it'll be there by the
> time my tables get huge ;-).  I see something about
> 	* Use indexes in ORDER BY, min(), max()(Costin Oproiu)
> in the TODO list, but is this actively being worked on, and will it
> solve my problem or just handle simpler cases?
> 
> Alternatively, is there a better way to do predecessor/successor
> queries in SQL?

Costin is not working on it currently, and I have removed his name from
the item.  I know of no one working on it, though it is requested every
so often.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 13 Jul 1998 17:31:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Anyone working on optimizing subset min/max queries?