Re: bad estimates

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