Hacker News Reader

Comment by Negitivefrags | Hacker News Reader

I have to say, my experience with Postgres is the opposite. Sometimes the query planner will suddenly decide to pick a bad execution plan.

I have been woken up in the night multiple times and found that the problem is that Postgres has arbitrarily decided to stop using an index and changed to do a full table scan and the database has ground to a halt under the load.

Doing an ANALYZE sometimes fixes it. Increasing the statistics target for the table in addition sometimes fixes it. One time we had to add an extra column to an index to make it decide to use it even though it shouldn't have needed it (and didn't a few hours before!).

The developers seem dead set against allowing you to override the query planner to add determinism.

I'm not saying MySQL is any better, as I have not used that in production before.

View on HN

Well if you grep for MySQL gotchas you should find a bunch of them. One did bite me hard one day. I wanted to add a simple check constraint on a column, as I did often on PG. A quick glance at the doc, I send the ADD CONSTRAINT command, and, no news is good news, I thought everything was all set and good. But in fact the beast did just ignore my command! After careful check in the docs in some case the constraints are ignored because not implemented.

That's crazy. How would you rate a shell that just said nothing and copied nothing when asked to copy an important backup file somewhere? For me it is the hell of software, not even telling there is something wrong.

The vessel is in danger when the captain don't know her position, but in much greater danger when he is not even knowing he doesn't know where she is.

You have to run your ANALYZE commands... I've run into this issue on very volatile tables (lots of inserts/deletes). With these, the table profile will change and without statistics the query planner might go on the wrong path.

That said, it's unfair to compare with MySQL's planner, which, although it is there, is so infantile that by comparison you can say MySQL has no planner. Example: Invariant sub selects run on every row of the master query, instead of being stored in a temporary table. Think SELECT a FROM foo WHERE foo.b IN (SELECT b FROM bar). Or another basic one SELECT a FROM foo ORDER BY b LIMIT 5 where there exists an index on b actually sorts the entire table instead of using the index and jumping out when it hits the LIMIT.

MySQL is much, much worse. It barely even has a query planner in the first place and it will often ignore perfectly good indexes.

Query planner changing its mind can be a problem for most DBs, Postgres is not special in this regard.

My experience with mySQL (admittedly none of that experience is recent so things may have changed a lot since) suggested that it didn't have a planner at all and it just attacked things in the order of your FROM/JOIN clauses using indexes where obvious from the WHERE clauses and linking ON definitions: it wouldn't for instance see circumstances where it could hit the second table of a join first and so need to consider for less of the first larger table for instance, so reordering inner joins in a query could make a significant difference.

I use MSSQL a lot in my day job and sometimes the query planner there surprises us by being fairly bright about this and other optimisations. Of course it sometimes gets things very wrong and you have to use index hints to push it in the right direction, but generally speaking if your queries are complex enough to hit that problem you might need to consider refactoring your design for other reasons too.

But the difference with MySQL is that you can easily forcibly make MySQL use the indexes you want it to, as long as it is possible.

With Postgres, while it usually seems to do a better job, when it refuses to use your indexes it can be a massive pain to coax it into doing what you want it to, even when you know it's making a bad choice.

Postgres has a number of issues like that, where ideological purity has a negative impact on usability. It still mainly use Postgres these days, but I regularly swear at it over things I know would be far simple than MySQL for those reasons (replication used to be a huge one, though that is now finally getting there, 8 years after I set up my first "large" replicated MySQL setup)

Postgresql's explain command actually, while cryptic, contains the full explanation behind the planners' decision. With that info, it's quite easy to force it into a given path.

Bad execution plans are almost always the result of a missing or incompatible index, bad table statistics (corrected by running ANALYZE) or bad server configuration (corrected by adjusting memory parameters and cost parameters).

The query planner evaluates multiple query plans, and chooses the one with the lowest cost based. If that results in the wrong plan, you should attempt to correct the query planner input (that is, the statistics and the server configuration, and possibly the tables and their indices) so that it can better evaluate the possible query plans.

EXPLAIN is a great tool to debug query plans.

I genuinely haven't had this problem and we have a fairly large instance. I'll keep my eyes peeled though. Thanks for the heads up.

Reply on news.ycombinator.com