ethanseal
Joined 6 years ago
Karma
110
Submissions
16
Member since
Nov 24
ethanseal.com
View on Hacker NewsRecent submissions
Comment · 1 year ago
Exactly the same one from what I see: https://github.com/ethan-seal/ors_expensive/blob/main/explai...
Given the buffer reads seem close to yours, I believe it's page cache.
Comment · 1 year 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/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-...
Comment · 1 year ago
Materialized views in Postgres don't update incrementally as the data in the relevant tables updates.^1
In order to keep it up to date, the developer has to tell postgres to refresh the data and postgres will do all the work from scratch.
Incremental Materialized views are _hard_. This^2 article goes through how Materialize does it.
MSSQL does it really well from what I understand. They only have a few restrictions, though I've never used a MSSQL materialized view in production.^3
[1]: https://www.postgresql.org/docs/current/rules-materializedvi... [2]: https://www.scattered-thoughts.net/writing/materialize-decor... [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...
Comment · 1 year ago
GIS is underrated. This is awesome!
Have you looked into speaking with the various SHPOs in each US State/Territory?
I've worked with several of them a fair bit and they have a ton of old maps hidden internally. Especially for small, specific areas of the state, like historical districts.
Comment · 1 year ago
I think having a way to build statistics on the join itself would be helpful for this. Similar to how extended statistics^1 can help when column distributions aren't independent of each other.
But this may require some basic materialized views, which postgres doesn't really have.
[1]: https://www.postgresql.org/docs/current/planner-stats.html#P...
Comment · 1 year ago
Highly recommend https://use-the-index-luke.com/
It's very readable - I always ask new hires and interns to read it.
Comment · 1 year ago
Cool. I'll have to read up on that.
Comment · 1 year ago
For sure, there's definitely a lot of cool techniques (and I'm not aware of all of them)! And the first example is very much contrived to show a small example.
I'm not super familiar with the term index merge - this seems to be the term for a BitmapOr/BitmapAnd?
Is there another optimization I'm missing?
The article links to my code for my timings here: https://github.com/ethan-seal/ors_expensive
There is an optimization that went in the new release of PostgreSQL I'm excited about that may affect this - I'm not sure. See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
Comment · 1 year ago
Gotcha, I misunderstood your comment. The multiple counts is a definitely very contrived example to demonstrate the overhead of BitmapOr and general risk of sequential scans.
Comment · 1 year ago
Absolutely. Though I don't recall seeing multiple sequential scans without a self-join or subquery. A basic filter within a sequential scan/loop is the most naive/simplest way of performing queries like these, so postgres falls back to that.
Also, fwiw, BitmapOr is only used with indexes: https://pganalyze.com/docs/explain/other-nodes/bitmap-or.