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

Re: bad estimates / non-scanning aggregates

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 / non-scanning aggregates
Date: 2003-08-29 02:50:38
Message-ID: 3F4EBF7E.8020602@speakeasy.org (view raw or flat)
Thread:
Lists: pgsql-performance
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 17:10:31 -0700,
>   Ken Geis <kgeis(at)speakeasy(dot)org> wrote:
> 
>>The query I want to run is
>>
>>select stock_id, min(price_date) from day_ends group by stock_id;
> 
> The fast way to do this is:
> 
> select distinct on (stock_id) stock_id, price_date
>   order by stock_id, price_date;

Not according to the optimizer!  Plus, this is not guaranteed to return 
the correct results.

  Unique  (cost=3711244.30..3795942.42 rows=6366 width=8)
    ->  Sort  (cost=3711244.30..3753593.36 rows=16939624 width=8)
          Sort Key: stock_id, price_date
          ->  Seq Scan on day_ends  (cost=0.00..361892.24 rows=16939624 
width=8)



In response to

Responses

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2003-08-29 02:51:09
Subject: Re: bad estimates / non-scanning aggregates
Previous:From: Bill MoranDate: 2003-08-29 02:15:20
Subject: Re: The results of my PostgreSQL/filesystem performance

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