Re: any way to use indexscan to get last X values

At 18:53 15.6.2003, you wrote:
>I've applied the attached patch to CVS tip to cure the latter problem.
>With this, a two-column index, and compatible column ordering in ORDER
>BY and GROUP BY, I get a reasonable-looking fast-start plan.  The patch
>will not apply exactly against 7.3 because there's a renamed function
>call in there, but you could make it work with a little effort.

You mean this:
/*
  * ordering_oper_opid - convenience routine for oprid(ordering_oper())
  *
  * This was formerly called any_ordering_op()
  */

A little later...

WOW!

100 to 130 times faster on same dataset and additional index on
(modifystamp,thread) which was not really useful before this patch!



krtjavendan34=>  EXPLAIN ANALYZE SELECT thread, modifystamp, count(id) AS
tcount,abstime(modifystamp) AS latest, max(id) as maxid FROM tjavendan
WHERE approved='Y'  GROUP BY modifystamp, thread ORDER BY modifystamp desc,
thread desc limit 40;
                                                                                QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..97.13 rows=40 width=12) (actual time=1.07..48.71
rows=40 loops=1)
    ->  Aggregate  (cost=0.00..20947.38 rows=8626 width=12) (actual
time=1.05..48.23 rows=41 loops=1)
          ->  Group  (cost=0.00..20516.06 rows=86265 width=12) (actual
time=0.35..42.25 rows=843 loops=1)
                ->  Index Scan Backward using tjavendan_modstamp_thrd on
tjavendan  (cost=0.00..20084.73 rows=86265 width=12) (actual
time=0.34..31.29 rows=844 loops=1)
                      Filter: (approved = 'Y'::bpchar)
  Total runtime: 50.20 msec
(6 rows)

Used to be between 5800 and 6741 msec before this patch!

Thanks!