3 ms·

I mean, great it catches more easy wins but until you do an EXPLAIN ANALYZE your plan analysis is worth sh*t (sorry I am highly skeptical of those solutions bas

by Sytten 3y ago

I mean, great it catches more easy wins but until you do an EXPLAIN ANALYZE your plan analysis is worth sh*t (sorry I am highly skeptical of those solutions based on personnal experience). I have seen so many cases where you add a "perfect" index for one query and it completely wreck the performances of another query because the planner decides to change and it makes a worst decision. It is very common in postgres to intentionally not add a good index or modify the query because we dont have query planner hints (rant for another day). We need better tooling around indexes but it needs to take into account all the queries and run them against the real dataset IMO. I think AWS Aurora plan manager is on the right track if it would be combined it with your tech.

MuffinFlavored 3y ago
how long until we can postgresql “query with this plan and these indices”?
- rrrrrrrrrrrryan 3y ago
  I'm a weirdo here on hacker news because I've mainly worked with Microsoft SQL databases my entire career, but it absolutely blows my mind when I remember that postgres can't lock in a query plan. It's so nice being able to sleep at night and not have to that an ultra important won't randomly shit the bed. One of my buddies that works at a PostgreSQL powered place once bitched about how they had a maintenance job that truncated a big table, then another job refreshed statistics, then the table rapidly filled up again and ask the query plans were awful because the table is usually massive. I get the religion behind the open source stuff: with SQL, you shouldn't have to tell the thing how to build the query plan, you should just tell it what you want and let it figure out how to do it. But if humans are expected to perform maintenance on the database, setting up scheduled jobs, refreshing statistics, identifying missing indexes, etc., then those same humans should be able to specify a query plan and/or specific indexes to use in a query.
  - tornato7 3y ago
    I worked at a company with a big, complex Postgres database behind the back end, probably half of all the incidents we had were related to the query planner suddenly deciding to change its approach. Eventually you get a vague idea of how to coax Postgres to make the plans you want it to, but the fact that you can't at least lock it in and the plan might change with any number of factors at any time... That's just bad design.
  - liquidpele 3y ago
    There’s a command to force it to update table statistics… you just run that, especially after doing manual stuff. Pretty common, but easy to forget.

by Sytten 3y ago

MuffinFlavored 3y ago

how long until we can postgresql “query with this plan and these indices”?

rrrrrrrrrrrryan 3y ago
I'm a weirdo here on hacker news because I've mainly worked with Microsoft SQL databases my entire career, but it absolutely blows my mind when I remember that postgres can't lock in a query plan. It's so nice being able to sleep at night and not have to that an ultra important won't randomly shit the bed. One of my buddies that works at a PostgreSQL powered place once bitched about how they had a maintenance job that truncated a big table, then another job refreshed statistics, then the table rapidly filled up again and ask the query plans were awful because the table is usually massive. I get the religion behind the open source stuff: with SQL, you shouldn't have to tell the thing how to build the query plan, you should just tell it what you want and let it figure out how to do it. But if humans are expected to perform maintenance on the database, setting up scheduled jobs, refreshing statistics, identifying missing indexes, etc., then those same humans should be able to specify a query plan and/or specific indexes to use in a query.
- tornato7 3y ago
  I worked at a company with a big, complex Postgres database behind the back end, probably half of all the incidents we had were related to the query planner suddenly deciding to change its approach. Eventually you get a vague idea of how to coax Postgres to make the plans you want it to, but the fact that you can't at least lock it in and the plan might change with any number of factors at any time... That's just bad design.
- liquidpele 3y ago
  There’s a command to force it to update table statistics… you just run that, especially after doing manual stuff. Pretty common, but easy to forget.