feat(blog): NOT EXISTS beats EXISTS by 20x — index size and heap fetches

Nikolay Samokhvalovrequested to merge
blog-not-exists-v2 into master

Closes #183 (closed)

Blog post: NOT EXISTS vs EXISTS with partial indexes.

Benchmark: PG18, 50M rows, 13 GiB heap, CCX33 (8 vCPU, 32 GiB), Hetzner NBG1

  • Index sizes: posts_not_deleted_id_key 1,050 MiB vs posts_deleted_id_key 22 MiB
  • Cold cache, 250 tag_ids: 22,574 ms vs 717 ms = 31x
  • Cold cache, 1,000 tag_ids: 63,750 ms vs 1,996 ms = 32x
  • Hot cache: 724 ms vs 161 ms = 4.5x

Raw results: postgres-ai/postgresql-consulting/tests-and-benchmarks#74

Edited by Nikolay Samokhvalov

Merge request reports