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

any way to use indexscan to get last X values with "order by Y limit X" clause?

From: Tomaz Borstnar <tomaz(dot)borstnar(at)over(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: any way to use indexscan to get last X values with "order by Y limit X" clause?
Date: 2003-06-15 14:26:36
Message-ID: 5.2.1.1.0.20030615161722.025c7630@127.0.0.1 (view raw or flat)
Thread:
Lists: 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



Responses

pgsql-performance by date

Next:From: Shridhar DaithankarDate: 2003-06-15 14:31:38
Subject: Re: any way to use indexscan to get last X values with "order by Y limit X" clause?
Previous:From: Nick FankhauserDate: 2003-06-14 14:40:30
Subject: A bit OT- RE: [PERFORM] Re-ordering .CONF params ... questions for this list

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