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:01:09 -0700
Message-ID: <3F4EFA35.90401@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>
Bruno Wolff III wrote:
> Can you do a \d on the real table or is that too sensitive?
It was silly of me to think of this as particularly sensitive.
stocks=> \d day_ends
Table "public.day_ends"
Column | Type | Modifiers
------------+--------------+-----------
stock_id | integer | not null
price_date | date | not null
open | numeric(9,4) |
high | numeric(9,4) |
low | numeric(9,4) |
close | numeric(9,4) |
volume | integer |
Indexes: day_ends_pkey primary key btree (stock_id, price_date)
Triggers: RI_ConstraintTrigger_16558399
> It still doesn't make sense that you have a primary key that
> is a stock and its price. What happens when the stock has the
> same price on two different dates? And I doubt that you are looking
> for the minimum and maximum dates for which you have price data.
> So it is hard to believe that the index for your primary key is the
> one you need for your query.
I can see the naming being confusing. I used "price_date" because, of
course, "date" is not a legal name. "day_ends" is a horrible name for
the table; "daily_bars" would probably be better. I *am* looking for
the mininum and maximum dates for which I have price data. I'm running
this query to build a chart so I can see visually where the majority of
my data begins to use as the start of a window for analysis.
When run on 7.3.3, forcing an index scan by setting
enable_seqscan=false, the query took 55 minutes to run. The index is
about 660M in size, and the table is 1G. As I mentioned before, with
table scans enabled, it bombs, running out of temporary space.
Hey Bruno, thanks for your attention here. I'm not a newbie, but I've
never really had performance issues with pgsql before. And I've been
running this database for a couple of years now, but I haven't run these
queries against it.
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: <3F4EFA35.90401@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