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

Re: Sequential Scan with LIMIT

From: Curt Sampson <cjs(at)cynic(dot)net>
To: John Meinel <john(at)johnmeinel(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sequential Scan with LIMIT
Date: 2004-10-25 07:17:07
Message-ID: Pine.NEB.4.58.0410251615150.597@angelic-vtfw.cvpn.cynic.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, 24 Oct 2004, John Meinel wrote:

> I was looking into another problem, and I found something that surprised
> me. If I'm doing "SELECT * FROM mytable WHERE col = 'myval' LIMIT 1.".
> Now "col" is indexed...
> The real purpose of this query is to check to see if a value exists in
> the column,...

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. You could also use more
standard and more set-oriented SQL while you're at it:

    SELECT DISTINCT(col) FROM mytable WHERE col = 'myval'

cjs
-- 
Curt Sampson  <cjs(at)cynic(dot)net>   +81 90 7737 2974   http://www.NetBSD.org
     Make up enjoying your city life...produced by BIC CAMERA

In response to

Responses

pgsql-performance by date

Next:From: Neil ConwayDate: 2004-10-25 07:24:54
Subject: Re: Sequential Scan with LIMIT
Previous:From: Tom LaneDate: 2004-10-25 01:50:34
Subject: Re: First set of OSDL Shared Mem scalability results, some

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