From: Ken Geis <kgeis@speakeasy.org>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
Date: Fri, 29 Aug 2003 00:57:43 -0700
Message-ID: <3F4F0777.8090503@speakeasy.org> (raw)
In-Reply-To: <20030829042453.GA5931@wolff.to>
References: <3F4E99F7.7080604@speakeasy.org>
<20030829025109.GD4373@wolff.to>
<3F4EBF7E.8020602@speakeasy.org>
<20030829030156.GF4373@wolff.to>
<3F4EC1D0.5010503@speakeasy.org>
<20030829033818.GA5577@wolff.to>
<3F4ECC78.7090502@speakeasy.org>
<20030829040519.GA5746@wolff.to>
<3F4ED1DC.50808@speakeasy.org>
<20030829042453.GA5931@wolff.to>
Sorry, all, to wipe out the context, but it was getting a little long.
Bruno Wolff III wrote:
> I am calling it quits for tonight, but will check back tomorrow
> to see how things turned out.
I went through the code (7.4 beta2) that estimates the cost of an index
scan path. What I need to be sure of is that when running a query in
pgsql that uses only the columns that are in an index, the underlying
table need not be accessed. I know that Oracle does this.
The cost_index function is assuming that after finding an entry in the
index it will be looking it up in the underlying table. That table is
not well correlated to the index, so it is assuming (in the worst case)
a random page lookup for each of 17 million records! In my case, if the
underlying table is indeed not touched, the estimated cost is 1000 times
the real cost.
63388.624000 to scan the index
67406506.915595 to scan the index and load a random page for each entry
Ken
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgeis@speakeasy.org, bruno@wolff.to
Subject: Re: bad estimates
In-Reply-To: <3F4F0777.8090503@speakeasy.org>
* 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