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 CF1A7D1B42F for ; Mon, 22 Dec 2003 15:29:46 +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 28934-10 for ; Mon, 22 Dec 2003 11:29:00 -0400 (AST) Received: from mailout04.sul.t-online.com (mailout04.sul.t-online.com [194.25.134.18]) by svr1.postgresql.org (Postfix) with ESMTP id E61CED1B44D for ; Mon, 22 Dec 2003 11:28:31 -0400 (AST) Received: from fwd04.aul.t-online.de by mailout04.sul.t-online.com with smtp id 1AYNn6-0004cf-03; Mon, 22 Dec 2003 12:00:00 +0100 Received: from router.azrael.de (XHtrgcZ1Qe5BoippkwgC4OGlA9YmT16ZUYhl4QH2-TxgYlIvjV4u4O@[80.141.232.167]) by fmrl04.sul.t-online.com with esmtp id 1AYNmu-1y12US0; Mon, 22 Dec 2003 11:59:48 +0100 Received: from azrael.azrael.de (azrael.azrael.de [192.168.202.18]) by router.azrael.de (8.11.4/8.11.4) with ESMTP id hBMAxkt15644; Mon, 22 Dec 2003 11:59:47 +0100 Date: Mon, 22 Dec 2003 11:59:58 +0100 From: Evil Azrael X-Priority: 3 (Normal) Message-ID: <110164283326.20031222115958@evilazrael.de> To: Ryszard Lach Cc: pgsql-performance@postgresql.org Subject: Re: "select max/count(id)" not using index In-Reply-To: <20031222103918.GA23673@siaco.id.pl> References: <20031222103918.GA23673@siaco.id.pl> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2 Content-Transfer-Encoding: 8bit X-Seen: false X-ID: XHtrgcZ1Qe5BoippkwgC4OGlA9YmT16ZUYhl4QH2-TxgYlIvjV4u4O@t-dialin.net X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/283 X-Sequence-Number: 5143 Guten Tag Ryszard Lach, Am Montag, 22. Dezember 2003 um 11:39 schrieben Sie: RL> Hi. RL> I have a table with 24k records and btree index on column 'id'. Is this RL> normal, that 'select max(id)' or 'select count(id)' causes a sequential RL> scan? It takes over 24 seconds (on a pretty fast machine): Yes, that was occasionally discussed on the mailinglists. For the max(id) you can use instead "SELECT id FROM table ORDER BY id DESC LIMIT 1" Christoph Nelles =>> explain ANALYZE select max(id) from ogloszenia; RL> QUERY PLAN RL> ---------------------------------------------------------------------- RL> Aggregate (cost=3511.05..3511.05 rows=1 width=4) (actual RL> time=24834.629..24834.629 rows=1 loops=1) RL> -> Seq Scan on ogloszenia (cost=0.00..3473.04 rows=15204 width=4) RL> (actual time=0.013..24808.377 rows=16873 loops=1) RL> Total runtime: 24897.897 ms RL> Maybe it's caused by a number of varchar fields in this table? However, RL> 'id' column is 'integer' and is primary key. RL> Clustering table on index created on 'id' makes such a queries RL> many faster, but they still use a sequential scan. RL> Richard. -- Mit freundlichen Grüssen Evil Azrael mailto:evilazrael@evilazrael.de