15,966,606 ms was the final tally. That's 4.453 hours...

What was the query?!

Old (15+ years?) stock analysis toy that I dug up. I made if for myself when I was first learning statistics.

Basically it was 1800 rows (of an 12M row dataset), joined against itself 6 times + 4 of those joins were "select max". This is a summary:

select ...
from  daily t
      inner join daily y on y.id = t.id and 
                            y.day =(select max(day) from daily where day < h.day)
      inner join daily h on h.id = t.id and 
                            h.day between t.day-100 and t.day-1
      inner join daily hp on h.id = t.id and 
                             hp.day = (select max(day) from daily where day < h.day)
      inner join daily f on f.id = t.id and
                            f.day between t.day+1 and t.day+100
      inner join daily hp on h.id = t.id and 
                             hp.day = (select max(day) from daily where day < h.day)
where price > 5 and close > 0 and...
group by ...
having ...




Not quite right.

I have it down to 1162ms: now I can safely run it against the full 12M rows.

12M rows only? I have 132 seconds on 39 billion rows using a simpler query but still.

+Roger Van Unen Like I said, "you know you are doing something wrong when ..." ;)

There were a few problems (one fixed):
1. The compounding of the query (I ran this agains the 12M rows and it took ~1 second, so the joins are no longer a significant part of the cost)
2. The select has a lot of aggregate queries, some of which are custom, most of which are unnecessary. I'm suspicious of this, especially when you watch the CPU usage vs Memory Usage
3. This is running on my laptop with a fresh install of Postgres: I haven't tuned any of the server resource allocations (low memory usage etc.). Though it appears this query is bottle-necking at the CPU (all those aggregrate functions.

At this point I get to say "fast enough", while I explore (what I think is) a deadlocking issue I created for myself </ rolleyes>

+Roger Van Unen you got me thinking about how big that query really was. With the 12M rows joined against itself 6 times, plus the nested max(date), I think that is 12M^10 permutations?

I don't even have names for those kinds of numbers. (6.19*10^16)

Your welcome +Jeff Cave. I would put the select Max() into a temp table so it is only gathered from disk once. And of cause try to decrease the nr of joins by calculating the values in advance.

Actually, I came up with a totally different mechanism for determining prior value, dropping the max() completely. That's what brought it down to 1 second.

Bravo +Jeff Cave 

Interestingly, this is the pre-calc query. The whole thing is being recovered from an 8 year old backup. The query in question is supposed to run every period (nightly) to pre-calculate some commonly used, but expensive values. It was never intended to be run against the full dataset all at once.

In the past, it has been my policy to not backup calculated values... since they can be recalculated if lost. My experience restoring this system has shown that, under some conditions, waiting for the recalc may not be feasible.

I certainly wasn't prepared to wait 1300 years (4.5 hours/period * ~2500 periods) for the dataset to be rebuilt. Testing of backups, as done in production systems, would have highlighted the problem.