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
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 |