Viewing profile — ethanseal

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.

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 po…

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 refres…

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…

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…

Highly recommend https://use-the-index-luke.com/ It's very readable - I always ask new hires and interns to read it.

Cool. I'll have to read up on that.

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 famili…

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.

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 p…