From: Bruno Wolff III <bruno@wolff.to>
To: Ken Geis <kgeis@speakeasy.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
Date: Thu, 28 Aug 2003 23:05:19 -0500
Message-ID: <20030829040519.GA5746@wolff.to> (raw)
In-Reply-To: <3F4ECC78.7090502@speakeasy.org>
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>
On Thu, Aug 28, 2003 at 20:46:00 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
>
> 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.
If you want both the max and the min, then things are going to be a bit
more work. You are either going to want to do two separate selects
or join two selects or use subselects. If there aren't enough prices
per stock, the sequential scan might be fastest since you only need to
go through the table once and don't have to hit the index blocks.
It is still odd that you didn't get a big speed up for just the min though.
You example did have the stock id and the date as the primary key which
would make sense since the stock id and stock price on a day wouldn't
be guarenteed to be unique. Are you absolutely sure you have a combined
key on the stock id and the stock price?
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: bruno@wolff.to, kgeis@speakeasy.org
Subject: Re: bad estimates
In-Reply-To: <20030829040519.GA5746@wolff.to>
* 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