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 03:46:00
Message-ID: 3F4ECC78.7090502@speakeasy.org (view raw or flat)
Thread:
Lists: pgsql-performance
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:00:32 -0700,
>   Ken Geis <kgeis(at)speakeasy(dot)org> wrote:
> 
>>Bruno Wolff III wrote:
>>
>>>>Not according to the optimizer!  Plus, this is not guaranteed to return 
>>>>the correct results.
>>>
>>>For it to be fast you need an index on (stock_id, price_date) so that
>>>you can use an index scan.
>>
>>I already said that such an index existed.  In fact, it is the primary 
>>key of the table.  And yes, I *am* analyzed!
> 
> 
> Your original example didn't actually match that of the table you are showing
> examples from. In that example the second half of the primary key was the
> date not the end of the day price. If this is the case for the real table,
> then that is the reason the distinct on doesn't help.

I had obfuscated the table in the example and forgot to do the same with 
the query.  Serves me right for thinking I care about that.

A big problem is that the values I am working with are *only* the 
primary key and the optimizer is choosing a table scan over an index 
scan.  That is why I titled the email "bad estimates."  The table has 
(stock_id, price_date) as the primary key, and a bunch of other columns. 
  What I *really* want to do efficiently is

select stock_id, min(price_date), max(price_date)
   from day_ends
  group by stock_id;

It is not the table or the query that is wrong.  It is either the db 
parameters or the optimizer itself.


Ken


In response to

Responses

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2003-08-29 04:05:19
Subject: Re: bad estimates
Previous:From: Eko PranotoDate: 2003-08-29 03:44:40
Subject: PostgreSQL HDD Grow capacity

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