This issue applies to postgresql 6.5.3 & 7.0
Say I have a table 'FACTURAS' (~400k rows), with
a 'RID' field, which is indexed with an BTREE index.
If I want to get the max(rid), the index is not
used:
=> explain select max(rid) from facturas;
NOTICE: QUERY PLAN:
Aggregate (cost=21139.66 rows=342414 width=4)
-> Seq Scan on facturas (cost=21139.66 rows=342414 width=4)
(yes, I run 'vacuum analyze').
I understand that the query planner cannot be so clever
to grasp that this particular function (max or min)
might be evaluated by just travelling the BTREE index.
Am I correct?
If I modify the query with a dummy restriction:
=> explain select max(rid) from facturas where rid>0;
NOTICE: QUERY PLAN:
Aggregate (cost=9582.90 rows=114139 width=4)
-> Index Scan using facturas_rid_key on facturas (cost=9582.90 rows=114139
width=4)
... the index is used, but only to get the restricted set of rows,
not to evaluate the maximum. Hence, the performance the same.
Cheers
Hernan Gonzalez
Buenos Aires, Argentina