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 121093A40D0 for ; Mon, 25 Oct 2004 08:25:28 +0100 (BST) 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 76246-01 for ; Mon, 25 Oct 2004 07:25:17 +0000 (GMT) Received: from sue.samurai.com (sue.samurai.com [205.207.28.74]) by svr1.postgresql.org (Postfix) with ESMTP id BF5A93A40FB for ; Mon, 25 Oct 2004 08:25:17 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by sue.samurai.com (Postfix) with ESMTP id AEF70197F4; Mon, 25 Oct 2004 03:25:17 -0400 (EDT) Received: from sue.samurai.com ([127.0.0.1]) by localhost (sue.samurai.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 30709-01-9; Mon, 25 Oct 2004 03:25:14 -0400 (EDT) Received: from [61.88.101.19] (unknown [61.88.101.19]) by sue.samurai.com (Postfix) with ESMTP id 8514B197F1; Mon, 25 Oct 2004 03:25:13 -0400 (EDT) Subject: Re: Sequential Scan with LIMIT From: Neil Conway To: Curt Sampson Cc: John Meinel , pgsql-performance@postgresql.org In-Reply-To: References: <417C023F.1080502@johnmeinel.com> Content-Type: text/plain Message-Id: <1098689094.13261.445.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 25 Oct 2004 17:24:54 +1000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com 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: 200410/408 X-Sequence-Number: 8884 On Mon, 2004-10-25 at 17:17, Curt Sampson wrote: > When you select all the columns, you're going to force it to go to the > table. If you select only the indexed column, it ought to be able to use > just the index, and never read the table at all. Perhaps in other database systems, but not in PostgreSQL. MVCC information is only stored in the heap, not in indexes: therefore, PostgreSQL cannot determine whether a given index entry refers to a valid tuple. Therefore, it needs to check the heap even if the index contains all the columns referenced by the query. While it would be nice to be able to do index-only scans, there is good reason for this design decision. Check the archives for past discussion about the tradeoffs involved. > You could also use more > standard and more set-oriented SQL while you're at it: > > SELECT DISTINCT(col) FROM mytable WHERE col = 'myval' This is likely to be less efficient though. -Neil