X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 79B4030DBFE for ; Sun, 15 Jun 2003 14:26:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 42111-03 for ; Sun, 15 Jun 2003 11:26:36 -0300 (ADT) Received: from game.over.net (unknown [193.189.169.9]) by svr1.postgresql.org (Postfix) with ESMTP id 7654930D6D9 for ; Sun, 15 Jun 2003 11:26:35 -0300 (ADT) Received: from localhost.over.net ([127.0.0.1]:9 "EHLO zvezek.over.net") by mail.over.net with ESMTP id ; Sun, 15 Jun 2003 16:26:35 +0200 Message-Id: <5.2.1.1.0.20030615161722.025c7630@127.0.0.1> X-Sender: tmail@127.0.0.1 X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Sun, 15 Jun 2003 16:26:36 +0200 To: pgsql-performance@postgresql.org From: Tomaz Borstnar Subject: any way to use indexscan to get last X values with "order by Y limit X" clause? Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200306/65 X-Sequence-Number: 2149 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