Обсуждение: 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@scn.ru

On Tue, Mar 11, 2003 at 15:42:41 +0700,
  Ruslan A Dautkhanov <rusland@scn.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;