Re: optimizer bent on full table scan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: optimizer bent on full table scan
Date: 2003-02-22 07:53:06
Message-ID: 12258.1045900386@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greg Stark <gsstark(at)mit(dot)edu> writes:
> I have a strange problem with the optimizer from CVS checked out as of about a
> week ago. It seems to be insisting on using a full table scan for a table
> lookup where the full table scan is about 20x slower than an index lookup.

> I think somehow it's being confused by the fact that some values of the index
> are populated and others haven't been so the distribution is odd.

Well, it's doing a heck of a good job of estimating the number of
matching rows --- can't complain about 8757 vs 8721. So there's some
other failure of modeling here. The only idea that comes to mind is
that maybe the rows matching foobar_id = 900 are tightly clustered in
the table, so that the planner's assumption of random fetches is overly
pessimistic. But the small correlation value says that there's not much
overall structure in the table's ordering. Can you shed any light on
that?

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Emmanuel Charpentier 2003-02-22 08:38:31 Re: How to update rows from a cursor in PostgreSQL
Previous Message Mike Mascari 2003-02-22 05:36:48 Re: HAVING and column alias