PostgreSQL adds windowing functions and common table expressions
As Hubert writes, PostgreSQL 8.4 has windowing functions.
Well done. I’ve been watching progress on this for a while. It greatly enhances the expressiveness of SQL.
What about common table expressions (WITH and WITH RECURSIVE)? Yes, PostgreSQL 8.4 will have common table expressions (WITH and WITH RECURSIVE) as well. That’s the “other” quantum leap in the expressiveness of SQL in my mind.
Meanwhile I’m helping a client rewrite subqueries and finding bugs in MySQL’s subquery handling that cause equivalent expressions to return different results. Alas, nested-loop left-deep query plans made it easier for MySQL to have different storage engines, but hard for it to go beyond the basics of expressiveness in SQL. (Warning: uninformed opinion alert. Maybe I’m blaming the wrong thing.)
This post should not be regarded as MySQL bashing or PostgreSQL glorifying. Use the tool that works for you, standard disclaimers, etc etc.
Hi!
hey, this is certainly interesting news! Thanks for posting.
Roland Bouman
21 Jan 09 at 9:17 pm
Can you elaborate on why left-deep nested-loops query plans makes it easier?
Mark Callaghan
22 Jan 09 at 12:39 am
Hi!
I don’t think using merge-sort join or hash join has a problem with the storage engine API. Both these algorithms require large buffers and sorting/hashing, much like those used when ORDERing BY or when using cross JOIN.
In that light, left-deep nested-index loops do not in particular make it easier for MySQL to have different storage engines.
As I understand it, the biggest disadvantage of having the storage engines API is the overhead of moving data from and to the storage engines. So a JOIN which combines MyISAM and InnoDB will be slower since it must be performed on the MySQL layer, not in the SE layer.
At any case this is my understanding only, not my inner knowledge. In fact, I’m not sure if and how specific storage engines can improve performance for queries which only relate to them (e.g. a multi-JOIN where all tables are InnoDB and such).
None of the above relates to SQL expressiveness. I’ll be interested to learn how indeed the JOIN mechanism affects expressiveness.
Regards
Shlomi Noach
22 Jan 09 at 1:55 am
@Shlomi: Well the other big disadvantage is that you need to effectively tune multiple RDBMS and allocate memory separately for caching etc.
Lukas
22 Jan 09 at 3:41 am
depesz.com has a good post on CTE’s in PG 8.4 as well:
http://www.depesz.com/index.php/2008/10/07/waiting-for-84-common-table-expressions-with-queries/
And the Postgres documentation:
http://developer.postgresql.org/pgdocs/postgres/queries-with.html
Andy
22 Jan 09 at 4:21 am
@Lukas,
Of course, you are right. I was referring to the SQL implementation part. Still other issues are with locking (how do you coordinate locks between different engines?), transactions (rolling back and synchronous commit for two different transactional engines), backup types and more.
Shlomi Noach
22 Jan 09 at 7:21 am
“Can you elaborate on why left-deep nested-loops query plans makes it easier?”
Hey, I had a disclaimer there! I love disclaimers.
I wasn’t being precise with my language. What I mean is something more like the following: A storage engine API in general isn’t to blame, but MySQL’s has caused a gripe or two from storage engine writers whose engines have extra intelligence that ought to live at a lower level than the server layer in MySQL. And I’m being cavalier in my original text, and conflating that with the difficulty the nested-loop plan causes for more intricate queries. Let me shift the blame and go completely off-topic to distract readers from my carelessness:
The nested-loop query plan may be part of the reason MySQL doesn’t currently support FULL OUTER JOIN. FULL OUTER JOIN is perfectly possible with the storage engine API. The server does IN() subqueries with the storage engine API in a suboptimal way.
Feel the cognitive dissonance! Just try forming a coherent argument against that!
Xaprb
23 Jan 09 at 3:14 pm
CTE’s is great : I discover this in Firebird 2.1 and it’s great SQL enhancement :)
VLDG
29 Apr 09 at 6:08 pm