I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as possible in this table so that we can construct month-to-month change reports that go as far back as possible.
for example, the data looks similar to this:
| p_key | monthend_id | val | |-------|-------------|-----| | 0 | 8/31/2018 | 100 | | 0 | 7/31/2018 | 90 | | 0 | 6/30/2018 | 110 | | 1 | 8/31/2018 | 25 | | 1 | 7/31/2018 | 31 | | 1 | 6/30/2018 | 22 |
To do this, I need to match the table to itself where the two are exactly 1 month apart. Right now I have a query that runs with a join like this:
SELECT * FROM monthend_table t1 LEFT JOIN monthend_table t2 ON t1.p_id = t2.p_id AND to_char(add_months(t1.monthend_source, -1), 'YYYY/MM') = to_char(t2.monthend_source, 'YYYY/MM')
This works fine, and I may even be able to take out the character formatting, however I notice in my execution plan that the index on
monthend_source isn't being used, and so it's doing 2 full scans.
How would I best construct indexes that would facilitate the fastest join possible?
Should I create one for
(p_id, to_char(monthend_source, 'YYYY/MM')) and another for
(pid, to_char(add_months(monthend_source, -1), 'YYYY/MM'))? Is a single index better?
I often find I struggle to know when and how to create effective indices in (what is to me) more complicated situations like these, so any advice would be greatly appreciated!