Re: Awkward Join between generate_series and long table

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Awkward Join between generate_series and long table
Дата
Msg-id CAKFQuwbfrcSWBUuzVoPvnQX9gOW64bUoEK0cxr+AgpHU3udk_g@mail.gmail.com
обсуждение исходный текст
Ответ на Awkward Join between generate_series and long table  (Lincoln Swaine-Moore <lswainemoore@gmail.com>)
Ответы Re: Awkward Join between generate_series and long table  (Lincoln Swaine-Moore <lswainemoore@gmail.com>)
Список pgsql-performance
Дерево обсуждения
On Wed, Nov 8, 2023 at 6:26 PM Lincoln Swaine-Moore <lswainemoore@gmail.com> wrote:
            SELECT
                s at time zone 'utc'  AS period_start,
                LEAD(s) OVER (
                    ORDER BY
                        s
                )   at time zone 'utc' AS period_end

Maybe doesn't help overall but this can be equivalently written as:
s + '1 day'::interval as period_end

Resorting to a window function here is expensive waste, the lead() value can be computed, not queried.
 
SELECT
    p.period_start,
    p.period_end,
    COUNT (distinct d.id)
FROM
    periods p
    LEFT JOIN data d
    ON
        d.timestamp >= (p.period_start)
        AND d."timestamp" < (p.period_end)
        AND d.sn = 'BLAH'

This seems better written (semantically, not sure about execution dynamics) as:

FROM periods AS p
LEFT 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 level

David J.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Lincoln Swaine-Moore
Дата:
Сообщение: Awkward Join between generate_series and long table
Следующее
От: Lincoln Swaine-Moore
Дата:
Сообщение: Re: Awkward Join between generate_series and long table