3 ms·
Is it possible to achieve this with a window function?
by karma_fountain 4y ago
Is it possible to achieve this with a window function?
- gavinray 4y agoI found out it is! Kudos to this kind internet stranger for telling me: https://stackoverflow.com/a/73129836/13485494 https://stackoverflow.com/a/73129836/13485494 But man is it a huge PITA (especially when doing programmatic code generation of the SQL) compared to LATERAL joins Someone familiar with the CockroachDB query planner showed me that a window function like this is what Cockroach turns LATERAL joins into for instance: demo@127.0.0.1:26257/movr> explain select * from abc, lateral (select * from xyz where x = a limit 2); • filter │ estimated row count: 1 │ filter: row_num <= 2 │ └── • window │ estimated row count: 2 │ └── • hash join │ estimated row count: 2 │ equality: (x) = (a) │ ├── • scan │ estimated row count: 6 (100% of the table; stats collected 2 minutes ago) │ table: xyz@xyz_pkey │ spans: FULL SCAN │ └── • scan estimated row count: 1 (100% of the table; stats collected 3 minutes ago) table: abc@abc_pkey spans: FULL SCAN