It's strange that it isn't sequential at least for the electric usage, as i've clustered using the index that it's using..
electricusage is the inner part of the nested loop, which means it will do ~3000 small scans. That's not sequential no matter how much you cluster. And the join order cannot be reversed (because you're filtering on premiseaccount).
I had work_mem set to 128mb. I tried bumping it to 1024mb, and I don't think I see a in the query plan.
128mb already is abusive enough. If anything, you'd have to lower it.
Looks like it is doing an index only scan for the first table, but not for the second. I tried creating two indexes that theoretically should make it not have to go to the physical table.: "electricusage_premise_account_id_36bc8999ced10059" btree (premise_account_id, from_date, usage) "ix_covered_2" btree (premise_account_id, from_date DESC, usage, id)
Any idea why it's not using that?
Index-only scans not only need the covering index, they also need fully visible pages. That takes time to build up.
If after that happens you're still getting poor performance, at that point, I guess you just have a lousy schema. You're trying to process way too scattered data too fast.
See, your query processes 15k rows, and reads 18k pages. That's as scattered as it gets.
The biggest table you've got there (from the looks of this query) is by far electricusage. You need to cluster that by bucket (since that's your querying criteria), but your schema doesn't allow that. I'm not sure whether it's viable, but if it were, I'd normalize bucket in premiseaccount and de-normalize electricusage to also refer to that bucket directly. That way, you can filter on electricusage, get a bitmap index scan, and live happily ever after.
Failing that, create materialized views, assuming your write patterns allow it.
And failing that, add more hardware. If linode doesn't provide it, move somewhere else.
