5 ms·

Quick question, how does one impact PG's query planner other than creating indexes (partial or otherwise)?

wiredfool 10y ago
You can intentionally do calculations to 'break’ indexes and take them out of consideration. (E.g., pg will tend to prefer scanning a primary key index if you're ordering by that in the query. Sometimes this is a very poor choice if there's a more selective index) There are also statistics that can be tweaked on vacuum analyze to improve the accuracy of the planner.
netghost 10y ago
There are different ways you can write queries that result in equivalent results. For instance you might try using inner joins, sub selects, lateral joins, etc.
radiowave 10y ago
Potentially, there was an index which the author knew would yield good performance for a particular query, but the query planner over-estimated the number of rows, and so decided that a sequential scan would be a better choice. By executing the command, "SET enable_seqscan = OFF;" inside a session, you can force postgres to use an index if it possibly can. These sort of problems can occur (for example) when very complicated joins are performed, or when unstructured data is being used as part of a join, as these tend to lead to poor estimates of the number of rows that the different parts of the query will yeild. Relevant docs here: https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...
- Groxx 10y ago
  For additional anecdotes, I've had to hint MySQL's planner twice, both times to exclude an index. It'd switch because the cardinality of one index was much better, but there were enough pathological cases (and they were the most-often used) that the planner's choices were proving fatal. It's not too abnormal. Sometimes you know the structure of your data better than the database's internal summary does, so you just give it a nudge in the right direction.
- williamstein 10y ago
  Here's exactly what happened: https://gist.github.com/williamstein/fb31e07d4057232bd3a3e780d6220482 https://gist.github.com/williamstein/fb31e07d4057232bd3a3e78...
sbuttgereit 10y ago
Another technique that gets used a fair amount to force certain planner behaviors are CTEs. I've used these to great effect in some rather complex queries where performance was critical. A good start to understanding how these work in an optimization context is: http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...

You can intentionally do calculations to 'break’ indexes and take them out of consideration. (E.g., pg will tend to prefer scanning a primary key index if you're ordering by that in the query. Sometimes this is a very poor choice if there's a more selective index) There are also statistics that can be tweaked on vacuum analyze to improve the accuracy of the planner.

netghost 10y ago

There are different ways you can write queries that result in equivalent results. For instance you might try using inner joins, sub selects, lateral joins, etc.

radiowave 10y ago

Potentially, there was an index which the author knew would yield good performance for a particular query, but the query planner over-estimated the number of rows, and so decided that a sequential scan would be a better choice. By executing the command, "SET enable_seqscan = OFF;" inside a session, you can force postgres to use an index if it possibly can. These sort of problems can occur (for example) when very complicated joins are performed, or when unstructured data is being used as part of a join, as these tend to lead to poor estimates of the number of rows that the different parts of the query will yeild. Relevant docs here: https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...

Groxx 10y ago
For additional anecdotes, I've had to hint MySQL's planner twice, both times to exclude an index. It'd switch because the cardinality of one index was much better, but there were enough pathological cases (and they were the most-often used) that the planner's choices were proving fatal. It's not too abnormal. Sometimes you know the structure of your data better than the database's internal summary does, so you just give it a nudge in the right direction.
williamstein 10y ago
Here's exactly what happened: https://gist.github.com/williamstein/fb31e07d4057232bd3a3e780d6220482 https://gist.github.com/williamstein/fb31e07d4057232bd3a3e78...

sbuttgereit 10y ago

Another technique that gets used a fair amount to force certain planner behaviors are CTEs. I've used these to great effect in some rather complex queries where performance was critical. A good start to understanding how these work in an optimization context is: http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...