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_16558399It 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
