News for hackers. Stuff that matters.
Re:Literate SQL by esaym on Saturday December 19, 2015 @12:34PM (#10764185)
In defense of the "Performance Considerations" against postgres, I worked on a team that had a BI guy that regularly wrote giant multi page queries using WITH statements.

The main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.

The query plans were mostly identical (neither one did much to optimize the WITH statements). The speed difference seemed to come from Postgres just accessing the disk better.

When doing an index scan, the disk read speed was about 80MBs, while DB2 was 10MBs. Full table scans in Postgres were between 180-220MBs while DB2 was around 60MBs. Same storage too (netapp over nfs). DB2 was using direct IO so it like to read in 4k chunks, but even disabling that didn't help much.

Re:Literate SQL by pgaddict on Saturday December 19, 2015 @12:52PM (#10764236)
The impact of CTEs being "optimization fences" in PostgreSQL really depends on workload, and how cleverly you write your queries. If you are not aware of this - as most users using CTEs merely as an alias - you'll be badly surprised.

I've been recently running TPC-DS on PostgreSQL 9.5 (alpha1 IIRC), to see how we're doing (I'm one of the contributors). Thanks to the grouping sets, we're now able to execute all 99 queries in the benchmark, which is awesome. Regarding performance, I've expected grouping sets to be a performance issue as the current implementation is rather simple. But to my mild surprise that's not the case - CTEs are the main problem.

On 100GB data set some queries took hours to complete (well, I interrupted them so I'm not sure how long it'd actually take). After a rewrite (replace CTE with a subquery), the query took a a minute or so.

Re:Literate SQL by radiowave on Saturday December 19, 2015 @01:02PM (#10764262)
A point that's well worth stating for those that may not realize it: rewriting a query that uses CTE into one that uses subqueries is really just a case of using copy and paste to perform substitution: replace the CTE references with their definitions. So, to a large extent you can have the best of both worlds.
Re:Literate SQL by jsprogrammer on Saturday December 19, 2015 @02:55PM (#10764629)
Which makes you wonder why Postgres can't just do that optimization for you.
Re:Literate SQL by alecdbrooks on Saturday December 19, 2015 @03:07PM (#10764658)
It's planned, apparently. [0] When it was discussed on the mailing list, it sounds like people were in favor of adding a keyword to disable it, making the syntax something like WITH UNBOXED (subquery) or WITH VIEW (subquery).

[0]: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor. Search for CTE.

Re:Literate SQL by pgaddict on Saturday December 19, 2015 @04:15PM (#10764868)
Because CTEs are not named subqueries, really. For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it.

Consider for example this:

WITH x AS (SELECT a, b FROM t) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);

Now, had this been evaluated using a merge join, the "x" CTE would have to be sorted first by "a" or "b" (for either side of the join). Well, that can't really happen.

Another issue is locking - consider this version, for example:

WITH x AS (SELECT a, b FROM t FOR UPDATE) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);

In other words, it's way more complicated than it might seem, especially if you can't break existing uses (e.g. the locking).

Re:Literate SQL by mistermann on Sunday December 20, 2015 @12:17AM (#10766145)
> For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it.

Not disputing what you're saying, but I swear I read something the other day recommending being careful using CTE's as they are re-executed once for every reference in the query (or something to that effect).

Re:Literate SQL by osolo on Saturday December 19, 2015 @04:37PM (#10764973)
It seems to me that these would be rare rather than the typical case. I think we could make the case that the more straightforward uses (like the ones in the articles) could be optimized away as described above. Leave the more complex/less optimized code path for these edge cases.
Re:Literate SQL by CuriousSkeptic on Saturday December 19, 2015 @05:59PM (#10765234)
SQL is a declarative language with semantics derived from relational algebra, which to me implies that there should be no execution semantics at all.

So the statement that "a CTE is only evaluated once" should not be part of any specification for the language. And I would not expect two different executions of a query to maintain that invariant if the optimizer finds it better not to.

Re:Literate SQL by pgaddict on Saturday December 19, 2015 @06:43PM (#10765382)
Yep, I agree with that.

The problem is we currently have an implementation that behaves as explained above (planned in isolation, evaluated once), and there are applications relying on that behaviour. We can't just change that without breaking them.

Re:Literate SQL by CuriousSkeptic on Sunday December 20, 2015 @07:03PM (#10768860)
True. At the same time you can't condemn future users to this suboptimal behavior just because a bad desicion in the past.

I'm not arguing for just abruptly reverse the design, but that the aim should be to reverse in an orderly manner with suitable deprecations and migration paths in place.

Re:Literate SQL by radiowave on Saturday December 19, 2015 @08:45PM (#10765706)
In order to be free of execution semantics, you'd have to disallow calling side-effectful functions within a query, which would be a major departure from the current behaviour.
Re:Literate SQL by calpaterson on Saturday December 19, 2015 @02:20PM (#10764528)
SQLAlchemy (which is sort of an embedded dsl for SQL) lets you give variable names to subqueries and as a result I don't really use CTEs. I bet the people using iBatis and JOOQ can do the same thing
Re:Literate SQL by lukaseder on Sunday December 20, 2015 @05:31AM (#10766682)
As far as using CTEs for structuring your SQL is concerned, you're right, unless people really want the side-effects CTEs sometimes have, such as materialisation in PostgreSQL.

But don't forget: CTEs can be recursive.

Re:Literate SQL by shrikant on Saturday December 19, 2015 @08:26PM (#10765647)
> We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned.

Big no-no right there -- a reporting database shouldn't have the same schema as your main transactional DB, because the latter is designed and built for write-heavy workloads, not reporting or analytical ones.

I'm pretty certain that a lot of the stuff happening in the CTEs could simply be designed as part of a "true" reporting schema and ETL'ed in as a batch job.

Re:Literate SQL by greggyb on Saturday December 19, 2015 @02:33PM (#10764565)
If a BI guy is writing big multi-page queries using CTEs that run for hours, that's a sign of numerous other problems.
Re:Literate SQL by CuriousSkeptic on Saturday December 19, 2015 @06:02PM (#10765243)
That's just unfair, I bet you get to write thousands of lines of code with execution times measured in weeks.
Re:Literate SQL by greggyb on Saturday December 19, 2015 @06:25PM (#10765317)
Not at all. As my profile states I work as a BI consultant. If you want to consider the entirety of an organization's ETL processing as a monolith, then overnight is really the longest execution time frame I'm allowed to talk about. I can also guarantee that if I were to propose a full 8 hours of ETL, I would be required to put up some serious justification.

That being said, my focus in my company is more on the front end. In this case there's a range of acceptable timeframes, but we've had clients with a strict requirement that any report with exposure to VPs or higher has to have <=2 second response time. Even outside of that organization, more than a minute for a report is usually pretty much a non-starter.

The problems I'm referring to have nothing to do with a BI guy implementing complicated logic; this is just a part of the job description. Let me quote the section where it becomes clear that there's a huge problem.

> The main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.

Emphasis is mine. They're reporting on an OLTP schema. The reason that the query is many pages long, and that it takes 24 hours to run, is because the CTEs are being used as in-line ETL. Their "reporting" database is nothing of the sort, it's just a replica of their operational system.

That poor BI guy is either out of his depth and doesn't know how or is simply not allowed to build the appropriate solution. Both are problems, and both extend far beyond the BI guy.

Edit: Extra word word.

Re:Literate SQL by esaym on Saturday December 19, 2015 @08:36PM (#10765692)
I could write pages on the "company's problems" but I don't feel like it. The "reporting" database was my idea and my attempt to actually be able to do sometime "cool" there.

The java software devs, even though they all had 15+ years experience, really had no clue what a database was other than a thing to put data in. App environment configs, customer configs, binary images,... you name it and it was stuck in the main database. The schema was designed by a sysadmin that left shortly after the company (a start up) actually started getting real customers. Messing with the schema was basically off limits. No one saw an issue with it.

Everyone thought the schema was brilliant because that sysadmin thought it would be a cool idea to replace indexes with partitions (DB2 9.7 at the time had a brand new "partition by range" feature). He wrote giant bash scripts to partition many tables by timestamps on either a 15 minute, one hour, or one day interval AND by groups of account IDs (or any other ID that might be in the row). His logic was that even though all queries will do a full table scan.. but hey these are all really tiny tables now! Management and the senior devs thought it was a brilliant idea. It didn't work so well in the end, and ultimately indexes were just added randomly all over the place. But the partitions remained and the scripts would run every hour... but sometimes they'd fail to add partitions because of locks. Which ultimately left no where for certain data to go (based on timestamps) and the app would go down. This went on for years...

But the other issue, as time rolled on, when the schema did need to be changed, it was done the quickest and dirtiest way possible. One patch I saw go through added a "username" column to several tables to track what user made a change to a row, but it was varchar instead of a FK... completely de-normalizing the schema. No one knew or understood the data on the dev team.

I tried best as I could to do analytics, but the DB2 DBA's were always on my case for "accessing" "their" database. I managed to get buy in from my manager to start standing up postgres databases for reporting. We tried Mysql at first but its supported SQL syntax was too butchered for anything other than simple queries.

The idea was to have a carbon copy of the OLTP schema in postgres and populate it with Pentaho or some other BI tools/scripts. Once that was mostly working we were going to build a data warehouse schema with stuff in the proper formats so we could just query without having to transform. Sadly after a few months my manager moved me off of the project and put a rather new contractor over the project who knew nothing about data architecture, or linux (he constantly complained about the command line and would say things like "I stopped using DOS years ago"), or even postgres. I was moved to simply help with some vmware upgrades which was boring. They then opened a "jr DBA" position and filled that with someone straight out of college to handle standing up the postgres instances.

And with that, I left.... and eventually most of the rest of the team too.

PS, they are looking for a BI guy...

Re:Literate SQL by greggyb on Saturday December 19, 2015 @10:52PM (#10765990)
Sounds like a nightmare.

You were definitely on the right track to set up an ODS and then stand up a DW in front of that. Looks like you were firmly stuck in the "not allowed" camp and trying to work around absurd limitations.

> PS, they are looking for a BI guy...

No thanks.

I'm spoiled by being a consultant. Anyone who wants to pay our rates is already committed to making changes.

Re:Literate SQL by MrBuddyCasino on Saturday December 19, 2015 @03:32PM (#10764731)
No surprise here, DB2 is the slowest of the big databases. It is also a steaming piece of shit.
Re:Literate SQL by assface on Saturday December 19, 2015 @12:45PM (#10764212)
> The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.

You're doing it wrong. These type of queries should be run on a column store (e.g., Vertica).

Re:Literate SQL by esaym on Saturday December 19, 2015 @01:40PM (#10764404)
All the money was flowing to IBM licenses. Hence when we needed another database for reporting, we went with open source. Vertica is big money++ And really, after my stint with that company, I really can't stand commercialized licensed software anymore.
Re:Literate SQL by CuriousSkeptic on Saturday December 19, 2015 @06:04PM (#10765247)
Did you look inte monetdb? I'm curios about real world experiences with that one.
Re:Literate SQL by greggyb on Saturday December 19, 2015 @06:37PM (#10765359)
The problem isn't the technology here. The problem is that they were using a replica of the OLTP database for reporting purposes.

OLTP and OLAP are hugely different workloads. A good 3NF (or close to it) schema that you'll find in an OLTP database is designed for fast writes with minimal locking; it fits well with the workload of OLTP.

If you're reporting on that data and performing analytical queries, then your schema needs to reflect that and optimize for large reads without much concern around lock contention for the primary workload.

I am not opposing the suggestion of a columnstore database, as we utilize these extensively with our clients. There is simply a much larger problem in the specified example. 15B rows is not too big a deal in an RDBMS for an analytical workload.