LATERAL top-N
LATERAL lets a subquery in FROM use columns of the tables to its left, so 'top N per group' becomes a short query with LIMIT.
task
For each of the 5 customers with most orders (ties by lower id), their 3 latest orders.
Play
lessons
aggregates
amcheck
analytics
btree
- [ ] a lookup reads few pages
- [ ] b tree meta page
- [ ] boss count a group from the index
- [ ] brin ranges
- [ ] gin pending list
- [ ] index only scans and heap fetches
- [ ] is not distinct from reaches the index
- [ ] physical correlation
- [ ] random inserts leave leaf pages loose
- [ ] skip scan on a b tree
- [ ] walk the root page
buffers
changing-data
copy
ctes-recursion
dates
explain
expressions
full-text-search
indexes
intro
joins
jsonb
locks
m1-catalogs
- [ ] boss one row about a table
- [ ] clear one extended statistics object
- [ ] clear statistics of a missing table
- [ ] column layout in pg attribute
- [ ] count your table s forks
- [ ] extended statistics on virtual generated columns
- [ ] pg stats knows the table oid
- [ ] statistics on a boolean function
- [ ] the index behind a primary key
- [ ] what pg class says about a table
m2-heap-pages
m3-mvcc
m4-vacuum
- [ ] a fresh file after repack
- [ ] a partitioned table needs an index name
- [ ] a read sets all visible without vacuum
- [ ] boss one vacuum cycle
- [ ] dead item memory in vacuum verbose
- [ ] line pointers across delete and vacuum
- [ ] relfrozenxid advances
- [ ] repack concurrently
- [ ] repack using index orders the heap
- [ ] the visibility map after vacuum
- [ ] vacuum full gives the space back
- [ ] which table would autovacuum pick
m5-hot
m6-toast
null
p10-partitioning
p11-rls-roles
p8-functions-triggers
p9-views
planner
- [ ] an impossible predicate
- [ ] coalesce stops at the first not null argument
- [ ] incremental sort under append
- [ ] is not distinct from reaches the index
- [ ] is null on any not null column of the inner side
- [ ] memoize under an anti join
- [ ] read stream counters in explain io
- [ ] read the memoize estimate
- [ ] turn eager aggregation off
ranges
schema-design
sequences-identity-uuid
set-operations
statistics
subqueries
transactions
types
wal
what-changed-in-19
- [ ] aggregate support clause
- [ ] base64url token
- [ ] boss pg19 table repair
- [ ] check not enforced promote
- [ ] checkpoint options
- [ ] conflict select needs returning
- [ ] copy header n
- [ ] copy json header error
- [ ] copy multiline header out
- [ ] copy partitioned roundtrip
- [ ] copy set null
- [ ] distinct from null folding
- [ ] eager aggregate
- [ ] grant granted by
- [ ] group subquery expr
- [ ] group subquery grouping
- [ ] inet gist default
- [ ] is json domain
- [ ] jit off
- [ ] json array empty
- [ ] jsonpath colors upper
- [ ] no crlf in names
- [ ] notin antijoin
- [ ] notin nullable
- [ ] oid8 validation
- [ ] psql display true
- [ ] psql search path prompt
- [ ] random dates
- [ ] regdatabase lookup
- [ ] repack concurrently
- [ ] repack index order
- [ ] repack partitioned needs index
- [ ] schema forward fk
- [ ] scs always on
- [ ] search path null
- [ ] skip scan
- [ ] stemmer dictionaries
- [ ] subscription server
- [ ] temporal oncall
- [ ] tid pages
- [ ] uuid from md5
- [ ] uuid from sha256 error
- [ ] virtual default
- [ ] virtual set expression
- [ ] virtual set expression violation
- [ ] wait for lsn
- [ ] win ignore rank error
editor
results
Run a query to see rows here.
Run EXPLAIN on the statement under the cursor: Plan tab, button Explain.
No tables yet.