Ну вот ссылка: https://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user
Хватит её? Если нет, лучше задавайте вопросы по ней.
Да, Вам нужны варианты с Recursive CTE, если что.

Оверхед приносит, но в среднем по сравнению с обращением к диску — копейки.
Впрочем, количественные сравнения (с простынёй/фиксированными числами и с диска/из кэшэй) достаточно несложны, по идее вам и карты, как говорится, в руки.

Тогда вопрос: есть ли смысл что-то менять в структуре, например? Как вообще люди действуют, когда надо постоянно гонять запросы на сравнения дат от клиентов в разных таймзонах? Если не хранить timestamptz, то что тогда? Или это тот случай, когда решение одно - железо?

Благодарю вас. Завтра разберусь с этим вопросом. На сегодня хватит.

Нормально в приведённом примере с индэксами в общем-то. В смысле — индэксы накладываются, если надо.

Я подозреваю, что люди используют нормальные запросы, а не " все происходит внутри проприетарного клиента, который предоставляет юзеру возможность оперировать заранее определенными функциями". ;)

> Если не хранить timestamptz, то что тогда?

Тогда — "получать неправильные результаты", почти наверняка.

Юзер пишет конструкцию вида DateOnly(targetdate) = AddDays(GetDate(), 15) потому что а) других конструкций в клиенте нет и б) юзер в sql все равно не умеет :)

А неправильные результаты хуже боли и страданий в данном случае :(

Откуда я знаю?

Вот что точно есть смысл — это выяснить, что не нравится.

а почему не timestamptz?

Не нравится скорость. 5-6 таких вот условий в where и запрос крутится по 20+ минут

Функциональный индекс можно наложить, но есть сомнение в его полезности (если только у них не большинство запросов с таким хитрым выражением)

Так вот это вот месиво:

(timestamp '1970-01-01 00:00:00' + trunc(cast(Extract(epoch from L1.targetdate at time zone 'Europe/Moscow' - timestamp '1970-01-01 00:00:00') / 86400 as numeric)) * 86400 * interval '1 second') at time zone 'Europe/Moscow' = (timestamp '1970-01-01 00:00:00' + Trunc(Extract(epoch from clock_timestamp() at time zone 'Europe/Moscow' - timestamp '1970-01-01 00:00:00') / 86400) * 86400 * interval '1 second') at time zone 'Europe/Moscow' + cast(text(15 * 86400) as interval) + (extract(timezone from (timestamp '1970-01-01 00:00:00' + Trunc(Extract(epoch from clock_timestamp() at time zone 'Europe/Moscow' - timestamp '1970-01-01 00:00:00') / 86400) * 86400 * interval '1 second') at time zone 'Europe/Moscow') - extract(timezone from (timestamp '1970-01-01 00:00:00' + Trunc(Extract(epoch from clock_timestamp() at time zone 'Europe/Moscow' - timestamp '1970-01-01 00:00:00') / 86400) * 86400 * interval '1 second') at time zone 'Europe/Moscow' + cast(text(15 * 86400) as interval)) ) * '1 second'::interval

даже на первый взгляд — чушь, и правильный результат возвращает разве что за счёт удачи, понимаете?
В сторону:

clock_timestamp

не в качестве отладочной функции — как это развидеть? ;(

а почему он крутится 20+ минут? он сильно быстрее работает в продакшене.

тут ни високосных секунд, ни переводов часов на вид не учтено

Это вопрос к вендору. Можно конкретно этот код переписать и вопрос - даст ли это что-то кроме упрощения кода

Подождите. Так корректность результата важна или всё-таки нет (я что-то запутался)?

Если не нравится скорость одного запроса — то нужно на него делать explain (analyze, buffers) и тогда ужэ решать насколько он отличается от оптимального и до чего его можно сократить, и вообще есть ли теоретическая возможность получить допустимый результат меньшэй сложностью вычислений.

короче, этот код точно работает неправильно на всех сутках длиной не 86400с

"осечек" (leap second, високосные года, етц) у этой простыни пока, за 5 лет, не случилось. То есть она таки корректно получает из timestamptz текущую дату по таймзоне клиента с нулями во времени. Но очень медленно, как мне кажется

clock_timestamp() я незаметил, каюсь. Да, это красиво.