X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3B20F5E46CD for ; Mon, 30 Aug 2004 16:30:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14601-03 for ; Mon, 30 Aug 2004 19:30:43 +0000 (GMT) Received: from boutiquenumerique.com (gailleton-2-82-67-9-10.fbx.proxad.net [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id F17F25E46CC for ; Mon, 30 Aug 2004 16:30:41 -0300 (ADT) Received: (qmail 28060 invoked from network); 30 Aug 2004 21:30:55 +0200 Received: from unknown (HELO musicbox) (192.168.0.2) by gailleton-2-82-67-9-10.fbx.proxad.net with SMTP; 30 Aug 2004 21:30:55 +0200 Date: Mon, 30 Aug 2004 21:32:28 +0200 To: pgsql-performance@postgresql.org Subject: Re: Why does a simple query not use an obvious index? References: <1093804128.5493.22.camel@localhost.localdomain> <87fz65k5sq.fsf@stark.xeocode.com> <16093.1093817023@sss.pgh.pa.us> From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <16093.1093817023@sss.pgh.pa.us> User-Agent: Opera M2/7.53 (Linux, build 737) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/454 X-Sequence-Number: 8090 Another primary key trick : If you insert records with a serial primary key, and rarely delete them or update the timestamp, you can use the primary key to compute an approximate number of rows. a := SELECT pkey FROM table WHERE timestamp() > threshold ORDER BY timestamp ASC LIMIT 1; b := SELECT pkey FROM table WHERE ORDER BY pkey DESC LIMIT 1; (b-a) is an approximate count. Performance is great because you only fetch two rows. Index scan is guaranteed (LIMIT 1). On the downside, you get an approximation, and this only works for tables where timestamp is a date of INSERT, timestamp worrelated wiht pkey) not when timestamp is a date of UPDATE (uncorrelated with pkey).