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
