> Maybe doesn't help overall but this can be equivalently written as:
Ah, so I've glossed over a detail here which is that I'm relying on some timezone specific behavior and not actually generate_series itself. If you're curious, the details are here: https://www.postgresql.org/message-id/2582288.1696428710%40sss.pgh.pa.us
s + '1 day'::interval as period_end
I think that makes the window function necessary, or at least something a little more sophisticated than addition of a day (though I'd be happy to be wrong about that).
> LEFT JOIN LATERAL (SELECT
Oh wow, this seems to get the index used! That's wonderful news--thank you.
I'd be super curious if anyone has any intuition about why the planner is so much more successful there--most of what I see online about LATERAL JOINs is focused as you said on semantics not performance. But in terms of solving my problem, this seems to do the trick.
Thanks again!
On Wed, Nov 8, 2023 at 5:45 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Wed, Nov 8, 2023 at 6:26 PM Lincoln Swaine-Moore <lswainemoore@xxxxxxxxx> wrote:SELECTs at time zone 'utc' AS period_start,
LEAD(s) OVER (
ORDER BY
s
) at time zone 'utc' AS period_endMaybe doesn't help overall but this can be equivalently written as:s + '1 day'::interval as period_endResorting to a window function here is expensive waste, the lead() value can be computed, not queried.This seems better written (semantically, not sure about execution dynamics) as:FROM periods AS pLEFT JOIN LATERAL (SELECT count(distinct? d.id) FROM data AS d WHERE d.timestamp >= p.period_start AND d.timestamp < p.period_end AND d.sn = 'BLAH') AS cnt_d-- NO grouping required at this query levelDavid J.
Lincoln Swaine-Moore
