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
