pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Sergio de Almeida Lenzi <lenzi@k1.com.br>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: sql query not using indexes
Date: Fri, 22 Sep 2000 12:51:32 -0400
Message-ID: <29455.969641492@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.BSF.4.10.10009211019360.62098-100000@megazone23.bigpanda.com>
References: <Pine.BSF.4.10.10009211019360.62098-100000@megazone23.bigpanda.com>

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> Ok I agree with you on the real database there are 127,300 rows and there
>> are certanly a great number of rows > 'AAA'. But, supose I make a query
>> select * from table where code > 'AAA' limit 10. it will read the entire
>> table only to give me the first 10 while in release 6.5 it will fetch the
>> index for the first 10 in a very fast manner, indeed the 6.5 release
>> resolves in 1 second while the 7.0 release resolves in 10-20 sec.

> Hmm, I believe Tom Lane was doing alot of stuff with the optimizer and
> limit but I don't remember if that was before or after the 7.0 release.
> It might be worth trying on current sources to see if that goes back to
> an index scan.

No, it'll still do a seqscan.  6.5 was in fact too ready to use
indexscans; the current code may have overcorrected a shade, but I think
it's closer to reality than 6.5 was.

As Hiroshi already commented, the difference in results suggests that
the desired data is very nonuniformly scattered in the table.  7.0
computes cost estimates on the assumption that the target data is
uniformly scattered.  For a sufficiently nonselective WHERE condition
(ie, one that the planner thinks will match a large fraction of the
table's rows) it looks better to do a seqscan and pick up the matching
rows than to follow the index pointers.  Adding a LIMIT doesn't change
this equation.

I like Hiroshi's recommendation: add an ORDER BY to help favor the
indexscan.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <29455.969641492@sss.pgh.pa.us>
Permalink:  ../29455.969641492@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29455.969641492@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, lenzi@k1.com.br
  Subject: Re: sql query not using indexes
  In-Reply-To: <29455.969641492@sss.pgh.pa.us>
  References: <Pine.BSF.4.10.10009211019360.62098-100000@megazone23.bigpanda.com> <29455.969641492@sss.pgh.pa.us>

* 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