Re: performance for MIN,MAX aggregates

Hello all,

Just a little simple example:

isbs=# \d radauth
Table "public.radauth"
Column | Type | Modifiers
----------+-----------------------------+-----------
dttm | timestamp(0) with time zone |
username | text |
realm | text |
logline | text |
Indexes: radauth_dttm_username btree (dttm, username)

isbs=# explain select min(dttm) from radauth;
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=2591.75..2591.75 rows=1 width=8)
-> Seq Scan on radauth (cost=0.00..2363.00 rows=91500 width=8)
(2 rows)

isbs=# \timing
Timing is on.
isbs=# select min(dttm) from radauth;
min
-------------------------------
Wed 15 Jan 00:10:35 2003 KRAT
(1 row)

Time: 1455,40 ms

As you can see, PostgreSQL use _sequential_ scans for determination of MINimal
datetime in the raduauth table, but index on dttm exists for this table.
Why not using index in the query?
btree indexes - is binary tree internally and questions like "fetch min/max
element table" can be done very quickly, without seqscans. Furhermore, even
touching table is unuseful in such cases - all required information can be
located in the index. Is it possible to force PostgreSQL use indexes
for MIN/MAX aggregate functions? Thanks a lot for any comments.

--
best regards,
Ruslan A Dautkhanov rusland(at)scn(dot)ru

On Tue, Mar 11, 2003 at 15:42:41 +0700,
Ruslan A Dautkhanov <rusland(at)scn(dot)ru> wrote:
> Hello all,
>
> Just a little simple example:
>
> isbs=# \d radauth
> Table "public.radauth"
> Column | Type | Modifiers
> ----------+-----------------------------+-----------
> dttm | timestamp(0) with time zone |
> username | text |
> realm | text |
> logline | text |
> Indexes: radauth_dttm_username btree (dttm, username)
>
> isbs=# explain select min(dttm) from radauth;
> QUERY PLAN
> --------------------------------------------------------------------
> Aggregate (cost=2591.75..2591.75 rows=1 width=8)
> -> Seq Scan on radauth (cost=0.00..2363.00 rows=91500 width=8)
> (2 rows)

This is discussed a lot in the archives. The short answer is use the
following query instead:
select dttm from radauth order by dttm limit 1;