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 C185630DB8F for ; Mon, 16 Jun 2003 06:16:53 +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 53269-09 for ; Mon, 16 Jun 2003 03:16:43 -0300 (ADT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id B649730DBE2 for ; Mon, 16 Jun 2003 03:16:41 -0300 (ADT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h5G6GeQ23513 for ; Mon, 16 Jun 2003 11:46:40 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h5G6Ge923508 for ; Mon, 16 Jun 2003 11:46:40 +0530 From: "Shridhar Daithankar" To: pgsql-performance@postgresql.org Date: Mon, 16 Jun 2003 11:45:52 +0530 MIME-Version: 1.0 Subject: Re: any way to use indexscan to get last X values Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3EEDADF0.29611.3901BC1@localhost> In-reply-to: <5.2.1.1.0.20030616002729.01bba2e0@127.0.0.1> References: <23329.1055695990@sss.pgh.pa.us> X-mailer: Pegasus Mail for Windows (v4.02) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Archive-Number: 200306/79 X-Sequence-Number: 2163 On 16 Jun 2003 at 0:37, Tomaz Borstnar wrote: > > 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 modifystamp, thread ORDER BY modifystamp desc, > thread desc limit 40; > QUERY > PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Limit (cost=0.00..97.13 rows=40 width=12) (actual time=1.07..48.71 > rows=40 loops=1) > -> Aggregate (cost=0.00..20947.38 rows=8626 width=12) (actual > time=1.05..48.23 rows=41 loops=1) > -> Group (cost=0.00..20516.06 rows=86265 width=12) (actual > time=0.35..42.25 rows=843 loops=1) > -> Index Scan Backward using tjavendan_modstamp_thrd on > tjavendan (cost=0.00..20084.73 rows=86265 width=12) (actual > time=0.34..31.29 rows=844 loops=1) > Filter: (approved = 'Y'::bpchar) > Total runtime: 50.20 msec > (6 rows) > > Used to be between 5800 and 6741 msec before this patch! Good that the patch works for you. But as I see there is an improvement in plan. Not nitpicking but what does actual performance difference between system before patch and after patch? Bye Shridhar -- QOTD: "In the shopping mall of the mind, he's in the toy department."