From: Neil Conway <neilc@samurai.com>
To: Curt Sampson <cjs@cynic.net>
Cc: John Meinel <john@johnmeinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential Scan with LIMIT
Date: Mon, 25 Oct 2004 17:24:54 +1000
Message-ID: <1098689094.13261.445.camel@localhost.localdomain> (raw)
In-Reply-To: <Pine.NEB.4.58.0410251615150.597@angelic-vtfw.cvpn.cynic.net>
References: <417C023F.1080502@johnmeinel.com>
<Pine.NEB.4.58.0410251615150.597@angelic-vtfw.cvpn.cynic.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: neilc@samurai.com, cjs@cynic.net, john@johnmeinel.com
Subject: Re: Sequential Scan with LIMIT
In-Reply-To: <1098689094.13261.445.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox