From: | Constantin Teodorescu <teo(at)flex(dot)ro> |
---|---|
To: | "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | An optimisation question |
Date: | 1999-08-30 06:50:18 |
Message-ID: | 37CA29AA.7C69D9D7@flex.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello,
I have a very big table (valori) with the following columns:
- data datetime
- debitor float8
- creditor float8
It has a btree index on data (non unique).
The following select is using the index:
select * from valori where data > '25-10-1999'
NOTICE: QUERY PLAN:
Index Scan using valori_data on valori (cost=1550.17 rows=24324
width=8)
But this one:
select data from valori order by desc limit 1
NOTICE: QUERY PLAN:
Sort (cost=3216.01 rows=72970 width=8)
-> Seq Scan on valori (cost=3216.01 rows=72970 width=8)
I thought that if the 'order by' implies an column which have a btree
index, the sort would not be actually executed and the index will be
used instead. But it seems that it won't.
Then, the question is : How should I retrieve extremely fast the first
'data' greater than a given value from that table.
Also, the following query :
select max(data) from valori where data<'2-3-1999'
is not using optimally the index, it just limit the records for the
aggregate function instead of picking the first value from the left of
the index tree lower than '2-3-1999'.
Waiting for some ideas,
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas IZ5 | 1999-08-30 09:58:47 | AW: [HACKERS] [Fwd: bug ? get_groname: group 0 not found] |
Previous Message | Hub.Org News Admin | 1999-08-30 02:11:24 |