Skip site navigation (1) Skip section navigation (2)

Re: Sequential Scan with LIMIT

From: Neil Conway <neilc(at)samurai(dot)com>
To: Curt Sampson <cjs(at)cynic(dot)net>
Cc: John Meinel <john(at)johnmeinel(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Sequential Scan with LIMIT
Date: 2004-10-25 07:24:54
Message-ID: 1098689094.13261.445.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
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



In response to

pgsql-performance by date

Next:From: Andrew SullivanDate: 2004-10-25 13:51:31
Subject: Re: Performance Anomalies in 7.4.5
Previous:From: Curt SampsonDate: 2004-10-25 07:17:07
Subject: Re: Sequential Scan with LIMIT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group