From: | leonbloy(at)sinectis(dot)com(dot)ar |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | btree index and max() |
Date: | 2000-06-01 18:08:31 |
Message-ID: | 200006011808.PAA03638@rye.sinectis.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ed Loehr | 2000-06-01 18:40:07 | Re: btree index and max() |
Previous Message | Steve Wampler | 2000-06-01 18:00:09 | Re: ALTERING A TABLE |