Comment by zepearl | Hacker News Reader

Comment by zepearl | Hacker News Reader

Parent Full thread

> 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.

Reply on news.ycombinator.com