PostgreSQL: Backend Flowchart | Hacker News Reader

PostgreSQL: Backend Flowchart

postgresql.org

202 pointsoctosphere8 comments

If you're looking for something with similar information to consume passively, check out Bruce Momjian's fairly detailed talk on Postgres internals:

https://www.youtube.com/watch?v=JFh22atXTRQ

My god, the table join optimizer can use a genetic algorithm. Fascinating. I would not have thought an SQL database would have a need for such an algorithm!

https://wiki.postgresql.org/wiki/Backend_flowchart#optimizer...

Genetic query optimizer optimizer/path evaluates all possible ways to join the requested tables. When the number of tables becomes great, the number of tests made becomes great too. The Genetic Query Optimizer considers each table separately, then figures the most optimal order to perform the join. For a few tables, this method takes longer, but for a large number of tables, it is faster. There is an option to control when this feature is used.

The default setting has the GA planner only kick in when you combine more than 12 tables in a select, if I remember correctly. This is because it's always slower than the standard cost-based planner.

I haven't seen a map tag in ages, and this seems like a proper use of it!

Given that the image here looks like it was generated from a .dot file, though, it could have just-as-well been exported as an .svg, where the SVG flowchart-box nodes themselves would have the fragment links attached to them.

As it is, given the comment on how to hand-calculate new <area> coordinates embedded near the <map> on the page, I think they just used Graphvis to output a .gif rendering of the flowchart, and then went in and hand-wrote a separate HTML <map> on top of it. Quite a bit more work, and work that would have to be duplicated if you ever wanted to tweak and re-export the graph itself.

Wow, that image GIF is such 90-ish design -- bright colors, awful unreadable fonts...

The vinn diagram of "people who understand PostgreSQL internals" and "people who are good at design" probably has a very small overlapped area.

Reply on news.ycombinator.com