5 ms·

> Failure to actually improve query performance: most of the time, the optimizer is actually right. my favorite response to someone complaining about a query d

> Failure to actually improve query performance: most of the time, the optimizer is actually right. my favorite response to someone complaining about a query doing a sequential scan is to tell them to `set enable_seqscan = false;` and rerun the query. usually, Postgres is right. it's rare that it is wrong, but it's nice to have a workaround for those few times that it's wrong - at least on fairly static data.

higeorge13 4y ago
Planner is usually correct given the information it has, the table statistics are usually off and are causing problems.
dspillett 4y ago
A common flaw to “just do x and rerun it” is that you are not running both in the same conditions: the data is in RAM second time around. I've seen people think that a change (an index hint, switching between a CTE and a sub-query, …) has made a massive difference because the last couple of times the query ran it timed out or took ages, when in fact the next run works and works faster not because of the change but because the previous run or two have primed the page cache with all the data that is needed, cutting out all the IO. Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved). Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).
iracic 4y ago
You can't check complex cases with that.
- jerrysievert 4y ago
  complex cases aren't usually thrown over a wall with "why is this doing a sequential scan?" typically, in the cases someone is asking me that specific question, it's a very simple query. complex cases, otoh, usually aren't approached with that much naivety.

higeorge13 4y ago

Planner is usually correct given the information it has, the table statistics are usually off and are causing problems.

dspillett 4y ago

A common flaw to “just do x and rerun it” is that you are not running both in the same conditions: the data is in RAM second time around. I've seen people think that a change (an index hint, switching between a CTE and a sub-query, …) has made a massive difference because the last couple of times the query ran it timed out or took ages, when in fact the next run works and works faster not because of the change but because the previous run or two have primed the page cache with all the data that is needed, cutting out all the IO. Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved). Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).

iracic 4y ago

You can't check complex cases with that.

jerrysievert 4y ago
complex cases aren't usually thrown over a wall with "why is this doing a sequential scan?" typically, in the cases someone is asking me that specific question, it's a very simple query. complex cases, otoh, usually aren't approached with that much naivety.