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 3156030DC43 for ; Sun, 15 Jun 2003 14:32:39 +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 45673-01 for ; Sun, 15 Jun 2003 11:32:28 -0300 (ADT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 39BB330D6D9 for ; Sun, 15 Jun 2003 11:32:27 -0300 (ADT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h5FEWRu25590 for ; Sun, 15 Jun 2003 20:02:27 +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 h5FEWQ925578 for ; Sun, 15 Jun 2003 20:02:27 +0530 From: "Shridhar Daithankar" To: pgsql-performance@postgresql.org Date: Sun, 15 Jun 2003 20:01:38 +0530 MIME-Version: 1.0 Subject: Re: any way to use indexscan to get last X values with "order by Y limit X" clause? Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3EECD0A2.8545.2FA1D9@localhost> In-reply-to: <5.2.1.1.0.20030615161722.025c7630@127.0.0.1> 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/66 X-Sequence-Number: 2150 On 15 Jun 2003 at 16:26, Tomaz Borstnar wrote: > > Here is the structure: > 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), > 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 Question. The field approved seems to have boolean values. If probability of having either of value is 50%, I doubt planner will use index anyway. Even assuming all possible values of a char variable, the choice isn't too much, say if you have 1M row. Correct me if I am wrong. Bye Shridhar -- Either one of us, by himself, is expendable. Both of us are not. -- Kirk, "The Devil in the Dark", stardate 3196.1