performance for MIN,MAX aggregates

From: Ruslan A Dautkhanov <rusland(at)scn(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: performance for MIN,MAX aggregates
Date: 2003-03-11 08:42:41
Message-ID: 3E6DA181.66FD5F4D@scn.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruno Wolff III 2003-03-11 11:38:55 Re: performance for MIN,MAX aggregates
Previous Message Tatsuo Ishii 2003-03-11 05:45:58 Re: [BUGS] Bug #908: Can not INSERT Cyrilic characters when use