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

On Sun, 15 Jun 2003, Tomaz Borstnar wrote:

> 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

I'm not sure that it'd help since I don't think it'd realize that it
doesn't actually need to completely do the group by due to the order by,
but in any case, in the above, the sort orders are different for the group
by and the order by and you'd really want a two column index on (probably)
(modifystamp, thread) in order to get the best results on replacing a
scan + sort.