Вопрос вроде популярный, но не смог найти ошибку в решении.

Нужно вытащить последнюю реинкарнацию организации - с максимальной org_version для данной org_id Почему postgres настолько ошибается в оценках и как сделать чтоб не ошибался?
rdb=# \d tt
Table "public.tt"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
org_id | integer | | |
org_version | integer | | |
Indexes:
"tt_id_version" btree (org_id, org_version)

rdb=#
rdb=# explain (buffers,analyze)
select * from tt d1,
(select org.org_id,
max(org.org_version) AS max_org_version
FROM tt org
WHERE org.org_id >= 0 GROUP by org.org_id) d2
where d1.org_id = d2.org_id and d1.org_version = d2.max_org_version;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=56.28..80.23 rows=6 width=16) (actual time=0.609..0.869 rows=847 loops=1)
Hash Cond: ((d1.org_id = org.org_id) AND (d1.org_version = (max(org.org_version))))
Buffers: shared hit=12
-> Seq Scan on tt d1 (cost=0.00..17.77 rows=1177 width=8) (actual time=0.012..0.074 rows=1177 loops=1)
Buffers: shared hit=6
-> Hash (cost=43.56..43.56 rows=848 width=8) (actual time=0.587..0.588 rows=847 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 42kB
Buffers: shared hit=6
-> HashAggregate (cost=26.60..35.08 rows=848 width=8) (actual time=0.391..0.485 rows=847 loops=1)
Group Key: org.org_id
Batches: 1 Memory Usage: 169kB
Buffers: shared hit=6
-> Seq Scan on tt org (cost=0.00..20.71 rows=1177 width=8) (actual time=0.007..0.128 rows=1176 loops=1)
Filter: (org_id >= 0)
Rows Removed by Filter: 1
Buffers: shared hit=6
Planning:
Buffers: shared hit=3
Planning Time: 0.352 ms
Execution Time: 0.959 ms
(20 rows)

rdb=#

И Вы нашли для этого один из наихудших способов. ;( Почему бы не попробовать что-то получше (LATERAL, если есть таблица организаций; loose index scan (rCTE) или DISTINCT ON, если нет)? И, кстати, что б ему и не ошибаться в этом случае (в таблице даже ни одного ключа нет)? ;)

Я вот эти все извращения не очень понимаю. Но даже если сделать уникальный индекс на оба поля - всё равно ошибается. Надо объявить его как Primary Key?

Не понимаете — сидите с [в среднем так бывает] производительностью на два порядка (или более) худшей, чем могла бы быть — дело Ваше. ;( > всё равно ошибается. А ANALYZE выполняли? И вообще, это план на реальных данных (иначе обсуждать это я практического смысла не вижу)?

В реальности таблица шире, число строк точное. Analyze делал

Тогда нужно копаться с конкретными данными и смотреть, как планировщик оценивает этот JOIN (но "чудо" там вряд ли будет — подобные оценки в принципе довольно грубы)...

А, собственно, почему? По-моему, как в его случае (когда org_version в среднем не сильно большэ одного на org_id) -- вменяемый довольно способ. Хотя, конечно... Планировщик за такое спасибо не скажэт.

Зачем для каждой организации свой счетчик, можно же сделать один на всех и брать его max + фильтр по org_id?

Потому что объективные измерения (benchmarks) альтернативных способов (даже для разных распределений данных) показывают, что это так — погуглите или попробуйте.

Кстати, если чаще всего нужэн максимальный org_version -- то имеет смысл начинать с создания индэкса (org_id, org_version DESC) Чуть-чуть быстрее будут типичные операцыи и чуть-чуть лояльнее к нему отнесётся планировщик.

Да на какие два порядка, не будет там 10мкс на 1000 строк хоть обпишысь...

Кстати, я правильно помню, что самый поддержываемый синтаксисом DISTINCT ON -- до сих пор не умеет index skip scan?

И, в итоге, самый быстрый результат таких запросов -- пишэтся через WITH RECURSIVE ? (Но и то -- не за счёт того, что планировщик там что-то понимает, а как раз наоборот).

Где я говорил именно про 1000 строк?! Я написал "в среднем так бывает", безотносительно к конкретному примеру (я вообще подумал, что это просто тестовые данные / демонстрация).

Так такого executor node до сих пор не существует — как он может его "уметь"? ;(

Самый быстрый (в норме) — LATERAL (или DISTINCT ON, если на каждое значение... org_id, в этом случае, приходится только 2-3 rows).

Ну, мало ли... На WITH RECURSIVE разложыть, например... Да и вообще. Есть многое на свете, друг Горацый, что мне лично и не снилось.

Не, все ждут, пока https://wiki.postgresql.org/wiki/Loose_indexscan кто-то "честно" реализует, зачем нам полумеры? ;)