[karma_fountain]

Is it possible to achieve this with a window function?

[gavinray]

I found out it is! Kudos to this kind internet stranger for telling me:

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