X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ECA32D1B456 for ; Mon, 22 Dec 2003 10:57:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 92280-08 for ; Mon, 22 Dec 2003 06:56:58 -0400 (AST) Received: from relay01.kbs.net.au (relay01.kbs.net.au [203.220.32.149]) by svr1.postgresql.org (Postfix) with ESMTP id 43F2AD1B496 for ; Mon, 22 Dec 2003 06:56:55 -0400 (AST) Received: from [203.221.246.88] (helo=familyhealth.com.au) by relay01.kbs.net.au with esmtp (Exim 3.36 #2) id 1AYNk6-0005RM-00; Mon, 22 Dec 2003 21:56:55 +1100 Message-ID: <3FE6CDF2.10608@familyhealth.com.au> Date: Mon, 22 Dec 2003 18:56:50 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ryszard Lach Cc: pgsql-performance@postgresql.org Subject: Re: "select max/count(id)" not using index References: <20031222103918.GA23673@siaco.id.pl> In-Reply-To: <20031222103918.GA23673@siaco.id.pl> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/280 X-Sequence-Number: 5140 > I have a table with 24k records and btree index on column 'id'. Is this > normal, that 'select max(id)' or 'select count(id)' causes a sequential > scan? It takes over 24 seconds (on a pretty fast machine): > > => explain ANALYZE select max(id) from ogloszenia; Yes, it is. It is a known issue with Postgres's extensible operator architecture. The work around is to have an index on the id column and do this instead: SELECT id FROM ogloszenia ORDER BY id DESC LIMIT 1; Which will be really fast. Chris