Thread

An optimisation question

Constantin Teodorescu <teo@flex.ro> — 1999-08-30T06:50:18Z

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