My single biggest beef about PG is the lack of query planner hints.
Unplanned query plan changes as data distribution shifts can and does cause queries to perform orders of magnitude worse. Queries that used to execute in milliseconds can start taking minutes without warning.
Even the ability to freeze query plans would be useful, independent of query hints. In practice, I've used CTEs to force query evaluation order. I've considered implementing a query interceptor which converts comments into before/after per-connection settings tweaks, like turning off sequential scan (a big culprit for performance regressions, when PG decides to do a sequential scan of a big table rather than believe an inner join is actually sparse and will be a more effective filter).
This looks very interesting. I had real difficulty where I needed both a btree and gin(pg_trgm) index on the same column. When using `like` postgres would consistently choose the btree index which resulted in performance that was something like 15secs as opposed to the 200ms or so I'd see if the gin index were used. In the end I added two separate columns, one for each index so that I could force the correct one to be used for a particular query.
ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. But at least you can be sure your query runs the way you intended.
An interesting approach and I'm not sure if I'd prefer it (I happen to like my queries being optimized automatically for my very tiny databases). But wouldn't it be possible to modify PostgreSQL to work this way too? It's unclear why you'd want to switch to a whole new DBMS for this.
Well, you're better off not doing joins at all in ClickHouse, beyond small dimension tables. Don't do joins between two or more big tables at all, is generally the rule in analytics databases; instead, pre-join your data at insert time.
CH supports optimizations for low-cardinality columns, so you can efficiently store things like enums directly as strings, rather than needing a separate table for them.
> My single biggest beef about PG is the lack of query planner hints.
Same here.
I did evaluate if to use PG for my stuff, but not having any hint available at all makes dealing with problems super-hard and potential bad situations become super-risky (esp. for PROD environments where you'll need an immediate fix if things go wrong for any reason, and especially involving 3rd party software which might not allow you to change the SQLs that it executes).
Not saying that it should be as hardcore as Oracle (hundreds of hints available, at the same time a quite stubborn optimizer), but not having anything that can be used is the other bad extreme.
I'd like as well to add that using hints doesn't have to be always the result of something that was implemented in a bad way - many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc... (e.g. maybe just the previous "update"-sql could have changed the data distribution in one of the tables but statistics would not immediately reflect that change) and not being able to force the execution to be done in a certain way (by using a hint) just leaved me without any options.
MariaDB's optimizer can often be a "dummy" even with simple queries, but at least it provides some way (hints) to steer it in the right direction => in this case I feel like I have more options without having to rethink&reimplement the whole DB-approach each time that some SQL doesn't perform.
"many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc..."
Would you say the primary problem that you have with the planner is a misestimate of the number of rows input/output from a subplan? Or are you encountering other problems, too?
(not the OP but...) I have had 3 cases in the last year where a postgres instance with less than millions of rows per table has decided to join with fancy hash algorithms that result in tens of seconds per query instead of the 5ms that it would take when it uses nested loops (i.e. literally start with the table in the from clause, apply some where clause, join to next table, apply more where clause, join to next table, and so on)
I do believe the planner was coming up with vast mis-estimates in some of those cases. 2 of the 3 were cases where the fully joined query would have been massive, but we were displaying it in a paged interface and only wanted 100 rows at a time.
One was a case where I was running a “value IN (select ...)” subquery where the subquery was very fast and returned a very small number of rows, but postgres decided to be clever and merge that subquery into the parent. I fixed that one by running two separate queries, plugging the result of the first into the second.
For one of the others, we actually had to re-structure the table and use a different primary key that matched the auto-inc id column of its peer instead of using the symbolic identifier (which was equally indexed). In that case we were basically just throwing stuff at the wall to see what sticks.
I have no idea what we’d do if one of these problems just showed up suddenly in production, which is kind of scary.
I’m sure the postgres optimizer is doing nice things for us in places of the system that we don’t even realize, but I’m sorely tempted to just find some way to disable it entirely and live with whatever performance we get from nested loops. Our data is already structured in a way that matches our access patterns.
The most frustrating part of it all is how much time we can waste fighting the query planner when the solution is so obvious that even sqlite could handle it faster.
For context, I’ve only been using postgres professionally for about a year, having come from mysql, sql server, and sqlite, and I’m certainly still on the learning curve to figure out how the planner works and how to live with it. Meanwhile, postgres feature set is so much better than mysql or sql server I’d never consider going back.
The feature set from an application perspective is killer. I love window functions especially; all sorts of clever things can be done in a single query which would otherwise require painful self-joins or multiple iterated queries and application-side joins in less sophisticated dialects.
My favorite killer feature is jsonb_agg / jsonb_object_agg which let me pull trees of data in a single query without the exponential waste you’s get from cartesian products, and even deliver it to the frontend without needing to assemble the json myself.
The biggest problem I see is the planner not knowing the cardinality of columns in a big table after a join or predicate has been applied. You see this especially with aggregate queries rather than point queries.
That is, it decides that a sequential scan would be just peachy even though there's an inner join in the mix which in practice reduces the set of responsive rows, if it just constructed the join graph that way. The quickest route out of this is disabling sequential scan, but there's no hint to do that on a per-query basis. The longer route is hiding bits of the query in CTEs so the optimizer can't rewrite too much (CTEs which need MATERIALIZED nowadays since PG got smarter).
High total cardinality but low dependent cardinality - dependent on data in other tables, or with predicates applied to other tables - seems hard to capture without dynamic monitoring of query patterns and data access. I don't think PG does that; if it did, I think they'd sell it hard. It comes up with application-level constraints which relate to the data distribution across multiple tables.
Can you enumerate some use cases you've run into? Sometimes looking at the individual use cases leads to better features than trying to generalize too quickly. For instance, controlling join order might suggest a different solution than a cardinality misestimate or a costing problem.
Query plan freezing seems like an independently useful feature.
Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"?
The planner may be fooled due to a too small data sample, you may try:
ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 10000;
Can't you use the autovacuumer in order to kick an ANALYZE whenever there is a risk of data distribution shift?
ALTER TABLE table_name autovacuum_analyze_scale_factor=X,
autovacuum_analyze_threshold=Y;
It's pretty hard to fix a complex black-box query planner with an estimate of when another black box analyze command will fix it.
That said, if you have good monitoring, you can hopefully find out when a query gets hosed and at least have a chance to fix it.. it's not terribly often.
Don’t blindly set stats to 10000, an intermediate value between the default of 100 and the max of 10000 may give you the best plans; experiment to find out.
I don't understand. At ANALYZE time isn't, all other parameters being equal and adequate (costs, GEQO at max, effective_cache_size ...), the probability of obtaining a representative set of columns better with a larger amount of randomly-selected values? Then at planning time isn't the the devised plan of better quality?
Adding sampled values may be bad performance-wise, for example if the planner cannot take everything into account due to some margin/interval effect, and therefore produces the same plan using a bigger set of values. The random selection process may also, sometimes, select less-representative data in the biggest analyzed set. But how may it never lead to the best plans (which may be produced using a smaller analyzed set) or lead to (on average) worse plans?
First, your point about planning time is important, thanks for adding that.
Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.
Unplanned query plan changes as data distribution shifts can and does cause queries to perform orders of magnitude worse. Queries that used to execute in milliseconds can start taking minutes without warning.
Even the ability to freeze query plans would be useful, independent of query hints. In practice, I've used CTEs to force query evaluation order. I've considered implementing a query interceptor which converts comments into before/after per-connection settings tweaks, like turning off sequential scan (a big culprit for performance regressions, when PG decides to do a sequential scan of a big table rather than believe an inner join is actually sparse and will be a more effective filter).