Squeeze the hell out of the system you have

Re:Squeeze the hell out of the system you have by throwdbaaway on Sunday August 13, 2023 @11:39PM (#37117210)

Nice benchmark script. With EXPLAIN (ANALYZE, BUFFERS), I see that

* normalized/join version needs to read 5600 pages

* normalized/join version with an additional UNIQUE INDEX .. INCLUDE (type) needs to read 4500 pages

* denormalized version only needs to read 66 pages, almost 100x fewer

Related to this pagination use case, when using mysql, even the denormalized version may take minutes: https://dom.as/2015/07/30/on-order-by-optimization/

[ Parent | on HN ]

Re:Squeeze the hell out of the system you have by yashap on Monday August 14, 2023 @03:15PM (#37125490)

Ooh ty, will give that article a read! And yeah, that's really the trick to queries that are consistently fast, even with cold caches - read few pages :)

[ Parent | on HN ]