From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Constantin Teodorescu <teo(at)flex(dot)ro> |
Cc: | "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] An optimisation question |
Date: | 1999-08-30 14:33:32 |
Message-ID: | 5561.936023612@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Constantin Teodorescu <teo(at)flex(dot)ro> writes:
> 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.
That's fixed for 6.6. A workaround that partially solves the problem
for 6.5 is to add a dummy WHERE clause referencing the ORDER-BY item:
select data from valori where data > '1/1/1800'
order by data limit 1;
The WHERE is needed to get the 6.5 optimizer to consider the index
at all. In a quick test it seems this works for normal order but not
DESC order... you could try applying the backwards-index patch that
someone (Hiroshi or Tatsuo, I think) posted recently.
> 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'.
There's no prospect of that happening anytime soon, I fear; there is no
connection between aggregate functions and indexes in the system, and
no easy way of making one. This workaround works in current sources:
explain select data from valori where data<'2-3-1999'
order by data desc limit 1;
NOTICE: QUERY PLAN:
Index Scan Backward using valori_i on valori (cost=21.67 rows=334 width=8)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-08-30 15:04:12 | Re: [HACKERS] Why not sub-selects in targetlists? |
Previous Message | José Soares | 1999-08-30 13:19:25 | Re: [HACKERS] getting at the actual int4 value of an abstime |