Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tomaz Borstnar <tomaz(dot)borstnar(at)over(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: any way to use indexscan to get last X values with
Date: 2003-06-15 16:33:45
Message-ID: 20030615092752.C31532-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.



In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2003-06-15 16:53:10
Subject: Re: any way to use indexscan to get last X values with "order by Y limit X" clause?
Previous:From: Tomaz BorstnarDate: 2003-06-15 15:17:04
Subject: Re: any way to use indexscan to get last X values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group