Received: from tom.wettzell.ifag.de (tom.wettzell.ifag.de [193.174.166.191]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBIHEO699112 for ; Mon, 18 Dec 2000 12:14:25 -0500 (EST) (envelope-from dassing@wettzell.ifag.de) Received: from wettzell.ifag.de (john.wettzell.ifag.de [193.174.166.33]) by tom.wettzell.ifag.de (8.9.3/8.9.3) with ESMTP id RAA11079; Mon, 18 Dec 2000 17:13:47 GMT Message-ID: <3A3E45CA.E5A84504@wettzell.ifag.de> Date: Mon, 18 Dec 2000 18:13:46 +0100 From: Reiner Dassing Organization: BKG-Wettzell X-Mailer: Mozilla 4.75 [en] (WinNT; U) X-Accept-Language: de,en MIME-Version: 1.0 To: Bruce Momjian CC: Tom Lane , pgsql-sql@postgresql.org Subject: Re: Best database structure for timely ordered values References: <200012181646.LAA29528@candle.pha.pa.us> Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/199 X-Sequence-Number: 533 Bruce Momjian wrote: > > The larger problem is that count(*) doesn't use the index at all. It > just does a sequential scan of the heap table. Here is another example, which is even worse: select value from table where id=1 order by epoch desc limit 1; to ask for the last stored value. This request needs about 5 minutes to complete. (But please consider that the postgres server has some other requests to serve. The machine running this server is an alpha server with Tru64) The explain statements results in: explain select * from table where id=1 order by epoche desc limit 1; NOTICE: QUERY PLAN: Sort (cost=12692.74 rows=202175 width=16) -> Index Scan using wetter_pkey on table (cost=12692.74 rows=202175 width=16) EXPLAIN -- Reiner Dassing