any way to use indexscan to get last X values with "order by Y limit X" clause? - Mailing list pgsql-performance

Similar question was
http://archives.postgresql.org/pgsql-admin/2002-05/msg00148.php, but google
did not have answer for it.

Here is the structure:

    Column    |           Type           |      Modifiers
-------------+--------------------------+----------------------
  id          | integer                  | not null default '0'
  datestamp   | timestamp with time zone | not null
  thread      | integer                  | not null default '0'
  parent      | integer                  | not null default '0'
  author      | character(37)            | not null default ''
  subject     | character(255)           | not null default ''
  email       | character(200)           | not null default ''
  attachment  | character(64)            | default ''
  host        | character(50)            | not null default ''
  email_reply | character(1)             | not null default 'N'
  approved    | character(1)             | not null default 'N'
  msgid       | character(100)           | not null default ''
  modifystamp | integer                  | not null default '0'
  userid      | integer                  | not null default '0'
  closed      | smallint                 | default '0'
Indexes: tjavendanpri_key primary key btree (id),
          tjavendan_approved btree (approved),
          tjavendan_author btree (author),
          tjavendan_datestamp btree (datestamp),
          tjavendan_modifystamp btree (modifystamp),
          tjavendan_msgid btree (msgid),
          tjavendan_parent btree (parent),
          tjavendan_subject btree (subject),
          tjavendan_thread btree (thread),
          tjavendan_userid btree (userid)

Here is the query:
SELECT thread, modifystamp, count(id) AS tcount, abstime(modifystamp) AS
latest, max(id) as maxid FROM tjavendan WHERE approved='Y'  GROUP BY
thread, modifystamp ORDER BY modifystamp desc, thread desc limit 40

and explain analyze for it:

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 thread, modifystamp ORDER BY modifystamp desc,
thread desc limit 40;
                                                                   QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=18419.78..18419.88 rows=40 width=12) (actual
time=6735.06..6735.69 rows=40 loops=1)
    ->  Sort  (cost=18419.78..18441.34 rows=8626 width=12) (actual
time=6735.04..6735.25 rows=41 loops=1)
          Sort Key: modifystamp, thread
          ->  Aggregate  (cost=16777.53..17855.84 rows=8626 width=12)
(actual time=4605.01..6711.27 rows=2938 loops=1)
                ->  Group  (cost=16777.53..17424.52 rows=86265 width=12)
(actual time=4604.85..6164.29 rows=86265 loops=1)
                      ->  Sort  (cost=16777.53..16993.19 rows=86265
width=12) (actual time=4604.82..5130.14 rows=86265 loops=1)
                            Sort Key: thread, modifystamp
                            ->  Seq Scan on tjavendan  (cost=0.00..9705.31
rows=86265 width=12) (actual time=0.13..3369.28 rows=86265 loops=1)
                                  Filter: (approved = 'Y'::bpchar)
  Total runtime: 6741.12 msec
(10 rows)

This is on 7.3.3.

Having backwards reading of index would really help here.

Thanks in advance.

Tomaz