[dx034]

I really don't like Oracle from a DBA perspective but it's still often far ahead of PostgreSQL when it comes to query performance. In Postgres, the query structure can make huge differences in terms of performance and it can take a lot of tuning to find the right query to optimize performance (especially when subqueries are involved). Oracle (and SQLServer) are usually pretty good at optimizing the query exactly the right way, reducing development time by quite a bit.

[lukeschlather]

It's very convenient for Oracle that anyone making these sorts of claims is contractually barred from sharing any evidence.

[ashtonkem]

This is actually my experience with Oracle; I had a DBA to take the management pain from us, and we had the budget for the correct licenses and hardware. The query performance was phenomenal considering the absolutely crazy amount of data we threw at that thing.

That being said, I would not recommend Oracle without all of the above factors already in place. For most use cases, Oracle is more pain than it’s worth.

[AmericanChopper]

As somebody who does a fair amount of DBA work, Oracle is my favorite RDBMS to operate. Low level administration is easy to manage, the optimizer is very performant, and the plan management features make tuning easy, and statistics management much less risky (and it can be very risky).

I wouldn't use it on any of my own projects though, but only because I wouldn't want to pay for it, and because I have enough faith in myself to be able to manage the trickier bits of Postgres.

[ashtonkem]

To be fair, I have no idea how hard it was to manage Oracle vs. how much my DBA just liked to gripe. But I largely agree with you, except I would lean towards a managed service because I’m no DBA.

[rpedela]

PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?

[dtheodor]

It's very true today. The problem discussed here is performance on complex queries (e.g. subqueries), and the query planner plays a huge role in that. The Postgres query planner has various issues. Here are two recent posts talking about planner issues:

https://medium.com/@rbranson/10-things-i-hate-about-postgres...

https://www.cybertec-postgresql.com/en/things-could-be-impro...

Also JIT compilation, while very nice and a step in the right direction, is very barebones at the moment, hardly achieving its potential. Here's a long todo list of what and how to make efficient use of JIT in postgres, by the main author of the feature. https://twitter.com/AndresFreundTec/status/10025899696161996... Postgres release 12 did not add any JIT related improvements, and as far as I know no work has been done on it on 13 either.

[ksec]

For someone not following closely, What are the reasons behind the lack of work on JIT since then?

[ris]

The traditional view that the bottleneck for query performance is overwhelmingly disk throughput, which has become slightly less true with the advent of SSDs.

[thda]

> as far as I know no work has been done on it on 13 either.

You should check the latest commitfest. Improvements are coming, at a steady pace.

[sbuttgereit]

Are you sure?

I just made a very casual search of the most recently completed commitfest and I saw one entry ("JIT expression evaluation improvements") marked as "Moved to next CF" and in the upcoming one the only item planned was the one that was moved.

Maybe there's more there with less obvious names?

[thda]

I am. 13 release is planned in the 3rd quarter. The next commitfest is in july.

[unclebucknasty]

I would say performance is still an issue in certain cases. We had some long-running queries with views and subqueries running on a down level version of PG. We recently attempted an upgrade to 12 to address this and take advantage of the latest RDS generations. The ideal architectural solution would be to sunset the views (involving unions) in favor of a better normalized database, but you do what you can when you inherit legacy. And we're not talking about millions of rows here. It's a relatively small installment.

Anyway, to our surprise, not only did it provide little boost, but other queries that were simpler and previously caused no issues became inexplicably and intolerably slow.

We did not have time to go down the rabbit hole of query planning and explains, and being a highly dynamically queriable app, we didn't know what else was in store for other queries based on some combination of user inputs down the road.

So we rolled back. PG has otherwise been great for us. It really is the performance issue in certain situations that has caused pain.

One more note is with regard to what I would call extreme performance variability based on row counts. What I mean is there seems to be some threshold that, once crossed, causes some queries to go from perfectly fine to near-interminable. You expect some degradation as row counts go up, but here the performance behavior suddenly degrades in a nonlinear fashion. That kind of issue is difficult to tune.

[ibejoeb]

One of the big changes in 12 allows the optimizer to work properly with CTEs, which was a major barrier to using the more expressive language features. There a good writeup here: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

[doublesCs]

Thanks for that link.

> Historically we've always materialized the full output of a CTE query

Do you know if this means that CTEs are written to disk? I didn't think this was the case. (In the case of materialized views that qualifier means the view is written to disk).

[da_chicken]

Here "materialization" just means that the query planner treats it as an optimization fence. That means the system doesn't do any optimization between the CTE and the query referencing it. It would prepare the output of the CTE as a completely separate entity essentially as if you had dumped it to a temp table. It may not be written to disk if there was sufficient memory, but either way you're sacrificing any optimization. I believe that it would even materialize the CTE multiple times if it was referenced multiple times, but don't quote me on that.

On the other hand, if the same query were written with subqueries instead of with CTEs, then the query optimizer would not treat it as an optimization fence. If it could utilize indexes or rewrite the query to be relationally equivalent, it would do so.

Note that sometimes that optimization fence is beneficial. There are situations where it's better to create temp tables and run smaller simpler queries instead of running an extremely complex monolithic query because the query planner isn't perfect even with hints. You can still enable that optimization fence functionality in PostgreSQL if you need to, but it's generally pretty rare that it happens like this. Still, you'll see stored procedures for reports still using temp tables and even cursors sometimes because they can be made to perform better in certain situations.

[ibejoeb]

A simple example would be something like predicate pushdown:

    with mycte as (
        select a, count(b) 
        from foo 
        group by a
    )
    select *
    from mycte
    where a = 10;

There is enough information in the statement to plan it as

    with mycte as (
        select a, count(b) 
        from foo 
        where a = 10;
        group by a
    )
    select *
    from mycte;

Before, Postgres would have computed the aggregate for all values of `a`.

[dtheodor]

It's in-memory materialization, which will spill to disk if it doesn't fit in memory.

Search for "Materialize node" in https://www.postgresql.org/docs/12/using-explain.html

[mrits]

I left postgres v9 and have been working on Vertica. It's pretty surprising that Vertica came out with that WITH/WITHOUT MATERIALIZATION keywords 2 years ago.

[__s]

& more's coming. PG13 has improved hash aggregation[0] & List (which is used throughout) has been updated from being a linked list to being an array

0: https://www.postgresql.org/message-id/507ac540ec7c20136364b5...

[asah]

I'd really like to know this as well !!

[jcadam]

Specifically for geospatial queries we found Oracle Spatial to be inferior to PostGIS (which is much more widely used). Regular relational queries seemed to be no worse (admittedly, our needs in a database were somewhat modest in that regard).

Though, most of our team's expertise as far as databases went was in Postgres (including our DBA).

[Pxtl]

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

[da_chicken]

I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement.

The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always.

The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.

[sjwright]

I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush.

(Most recent version I've used was SQL Server 2008.)

[ants_a]

All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case.

As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.

[m_mueller]

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale horizontally. Granted, it's hard to design the data structures in the design space of normalization (many joins) vs. read performance (few joins) - but at least it's all laid bare to reason about.

[ants_a]

I somewhat agree. Not with the sweeping that declarative query languages can't work, for every case where faulty statistics cause a bad plan there are likely to be many cases where a statistic based plan choice correctly switched plans due to data distribution changes.

But I do agree that PostgreSQL has way too little tools to nail down the performance even though they have downsides. Tools like pinning execution plans would be nice, as it has less severe worst case behaviors. As would be the ability to just pass the execution plan directly, although that would have severe cross-version compatibility implications and security will also be hard to nail down after the fact because of all the "can't happen" assumptions sprinkled around in executor code. And even just plain plan hints would be great to have, be it the heavy handed "join in this order", "use this index", or the more graceful "this clause is way less selective than you think", "this clause is functionally dependent on that one" or "assume there is correlation between ordering and predicates".

[m_mueller]

I'd put it this way: It could be made to work, but IMO all current RDBMS have the same problems with terrible worst case guarantees. Query planners have not enough degrees of freedom - maybe with a new concept like automated indices it could be made to work with acceptable average and worst case guarantees.

[sjwright]

Sorry, but hard disagree. For all the failings of SQL systems, they work staggeringly well 99% of the time as long as the operator understands how they work. No-SQL systems do have their place, but all too often their choice stems from a failure to understand SQL-based databases. (And the hard truth: unless you’re doing something especially novel there’s a lot less difference between map/reduce and a bog standard table index than no-SQL proponents would have you believe.)

In my estimation, if you're not already intimately familiar with the intricacies of performant SQL, chances are you're not playing in a space where a no-SQL architecture is an appropriate fit anyway. And you're certainly not in a position to make an informed decision between SQL and no-SQL. It's a much better use of this hypothetical person's time to set up Mariadb or Sqlite and do a deep dive into the fundamentals query performance.

[m_mueller]

Just to clarify: I'm not saying there is a significant difference between map views and indices, I'm saying I prefer the directness of programming with them over trusting the query planner and table statistics. In couch, to do a table scan, I need to program it as such, otherwise I'm forced to use a view - things like forgotten indices for some edge cases are impossible. Sure takes longer to do things, but when performance issues arise it's easy to reason about while with SQL based DBs I've seen a lot of time being spent at that stage. They work well 99%, and then that remaining 1% takes 99% of your time.

[sjwright]

> All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't.

Every single time the SQL Server query optimiser did something obviously wrong, rebuilding statistics fixed it. The problem I had with SQL Server wasn't its reliance of statistics—it's not like they could work any other way—it's that it failed to maintain its statistics correctly. Any time that statistics stop being correct is a bug. It should be able to maintain them itself and trigger rebuilds whenever there's any doubt about them.

[carlosf]

You might even call it a Data Lake and get away with it.

[zip1234]

Does PostgreSQL have bitmap indexes. Very much a great feature that Oracle has for query performance

[atombender]

No. Postgres has in-memory bitmap indexes, which are built on the fly while scanning the index and used to more efficiently combine AND/OR clauses, but that's not quite the same thing. There have been several attempts at adding on-disk bitmap index support to Postgres, but they've all been abandoned.

[anarazel]

BRIN indexes are kind of bitmap like...

[fanf2]

Looks like PostgreSQL only has ephemeral bitmap indexes used when making a query that combines multiple indexes - https://leopard.in.ua/2015/04/13/postgresql-indexes - https://www.postgresql.org/docs/current/indexes-bitmap-scans...