Explain Extended

Ooohh, I didn't know this name for it, thanks. I somehow came across it as The Sultan's Riddle on the internet: https://explainextended.com/2016/12/31/happy-new-year-8/ I prefer this less sterile framing of it. It was the most fun that I ever had with a puzzle, so to anyone scrolling around on this page, I would recommend not jumping straight to the solution :)

I always loved Snowflakes[0] in postgresql. That said though, almost anything could be visualized in postgresql because of the awesome geometric[1] functions that are natively supported. Someday when I'm bored, I might try doing something like the Mona Lisa in postgres. [0] http://explainextended.com/2013/12/31/happy-new-year-5/ [1] http://www.postgresql.org/docs/9.0/static/functions-geometry.html

Ok here is the search that first brought this issue to my attention: mysql spatial index example lft rgt I see the following order: 1: http://planet.mysql.com/entry/?id=23512 2: http://efreedom.com/Question/1-1743894/Mysql-Optimizing-Finding-Super-… 3: http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-my… 4: http://stackoverflow.com/questions/1743894/mysql-optimizing-finding-su…

>> I've asked ChatGPT to write a VBScript function that returns false if the input is true and false if the input is false. <insert to-be-fair meme here> You purposefully asked GPT a trick question for which it likely has very little training data. No one would ever go on StackExchange et al. asking how to write a function that always returns false. So GPT had to take its training data on other, realistic questions and fumble something together. A GPT can only ever be as good as its initial training material. It can not have new ideas or innovations. There was an excellent post here a couple of days ago to an article, which explains quite well what a GPT can and can not do and why: https://explainextended.com/2023/12/31/happy-new-year-15/ EDIT. GPT4 was happy to give me a one-liner that meets the conditions and even explains why my request was dumb: https://chat.openai.com/share/1d949b5b-3c2b-4634-802e-3ea4c2a7a786

For the LLM explainer, did you point Claude at this one? https://explainextended.com/2023/12/31/happy-new-year-15/ This page Claude assisted page rhymes with that one. Sorta.

With Postgres you can also generate fireworks http://explainextended.com/2009/12/31/happy-new-year/ or snowflakes: http://explainextended.com/2011/12/31/happy-new-year-3/ Or you can solve a Sudoku riddle in Oracle https://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sud…

"It's full of jewels", as someone almost said. E.g. The Sultan’s Riddle in SQL https://explainextended.com/2016/12/31/happy-new-year-8/

Things like late row lookups in mysql that are not an issue in postgres https://explainextended.com/2011/02/11/late-row-lookups-innodb/

You can actually do really fun (and fast!) things with dates by mapping them to geospatial types. For an example see: http://explainextended.com/2009/08/11/efficient-date-range-query-sql-s… You can easily extend this map into more dimensions depending in your common use cases and the capability of your DB's spatial library.

I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN. I don't think this is true, unless it's a very recent change. Here's a post from 2009 comparing NOT IN/NOT EXISTS/LEFT JOIN WHERE IS NULL for Postgres: http://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-joi…

Sure, especially if you nest the sets using a datatype indexable with an R-Tree index: http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-my… However, nested sets is still very expensive to update which may be a deciding factor if your hierarchy is subject to frequent change (like, a folder-like structure with the folders being moved here and there) and you don't need to query for "all descendants" often.

> I was AMAZED how poorly Oracle performed in one certain use case (real time aggregation of a relatively large hierarchial database) Out of interest, what kind of hierarchial database was this? Something like this? http://explainextended.com/2009/09/28/adjacency-list-vs-nested-sets-or…

I like this style better than SQL for Smarties. Celko tries to stick with the ANSI standard, which is fine, but I much to prefer to know how to get the most out of each platform. Like here, http://explainextended.com/2009/09/, he covers adjacency list vs. nested sets on MySQL, Postgres, Oracle and SQL Server. Different platforms, different optimal solutions. The man knows his database internals.

First off - whatever you're doing, you'll probably be fine using either. That said, I'm generally more frustrated when using MySQL than when using PG. Here's a sample of the problems I've encountered from using both MySQL and PG. I haven't updated my list in a while now - please feel free to correct me on things - but hopefully it's a little more illustrative than that Wikipedia feature matrix, and a little more specific to MySQL vs. PG. (This list is a cleaned-up selection from my notes wiki at http://yz.mit.edu/notes/Hackery.) No referential integrity. No constraints (CHECK). No sort merge join, let alone hash-join. http://www.dbms2.com/2008/07/10/how-is-mysqls-join-performance-these-d… http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow… Generally poor at analytical workloads, since it's designed for transactional workloads. Can't reopen TEMP table - WTF? (Still not fixed!) http://bugs.mysql.com/bug.php?id=10327 Multiple storage engines has always restricted progress: http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-multiple-st… (PG also supported multiple storage engines in 80s, then concentrated on one) No WITH clause: http://stackoverflow.com/questions/324935/mysql-with-clause Crappy errors: “Incorrect key file for table ‘stock’; try to repair it” on “alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);” where stock is in InnoDB (which has no “repair table”) means I have no…