9 ms·

Not sure on which Postgres version this was tested with, but the first example runs in about 2ms with my Postgres 17 installation ("cold cache"). It uses a Bitm

• ethanseal 1y ago
  When you say cold cache, did you clear the os page cache as well as the postgres buffercaches? After setup.sql, the cache will be warmish - I get 4ms on the first run. I'm using postgres 17.5 See https://github.com/ethan-seal/ors_expensive/blob/main/benchmark.sh https://github.com/ethan-seal/ors_expensive/blob/main/benchm... where I use dd to clear the os page cache. This article by pganalyze talks about it: https://pganalyze.com/blog/5mins-postgres-17-pg-buffercache-evict https://pganalyze.com/blog/5mins-postgres-17-pg-buffercache-...
  • hans_castorp 1y ago
    I did not explicitly evict the Postgres buffer cache, but using pg_buffercache to evict all buffers for the table, yields a runtime of 23ms for me (still going for the BitmapOr). https://notebin.de/?ac3fcf55e6850f47#ERXndRrqp3X4zEWX5EC3dZUwdXzYhJd7QssK4dnSH3Kh https://notebin.de/?ac3fcf55e6850f47#ERXndRrqp3X4zEWX5EC3dZU... Which plan does Postgres choose in your case that results 100ms?
    • ethanseal 1y ago
      Exactly the same one from what I see: https://github.com/ethan-seal/ors_expensive/blob/main/explain_plans/cold_or_explain_plan https://github.com/ethan-seal/ors_expensive/blob/main/explai... Given the buffer reads seem close to yours, I believe it's page cache.