Message-ID: <3F4EFA35.90401@speakeasy.org>
Date: Fri, 29 Aug 2003 00:01:09 -0700
From: Ken Geis <kgeis@speakeasy.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.4) Gecko/20030612
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
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>
In-Reply-To: <20030829042453.GA5931@wolff.to>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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