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

Re: bad estimates

From: Ken Geis <kgeis(at)speakeasy(dot)org>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: bad estimates
Date: 2003-08-29 07:57:43
Message-ID: 3F4F0777.8090503@speakeasy.org (view raw or flat)
Thread:
Lists: pgsql-performance
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



In response to

Responses

pgsql-performance by date

Next:From: Tomasz MyrtaDate: 2003-08-29 07:59:10
Subject: Re: Indexing question
Previous:From: Alexander PriemDate: 2003-08-29 07:49:28
Subject: Re: Indexing question

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