Re:Hypothetical Indexes in PostgreSQL
by derefr on Tuesday July 02, 2019 @07:09PM
(#20340198)
> In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough.
This assumes a use-case where there is a "site" (i.e. a business layer in front of PG that can abstract away deficiencies in the query plan by adding e.g. caching.)
If you're directly working with PG, interactively writing OLAP reporting queries, you can have "working queries" that take ~300s (still practical enough to build your report) switch to taking milliseconds after an autovacuum (if, for example, a table being joined against was recently populated via ETL and hadn't been vacuumed yet.) People do notice that.
This assumes a use-case where there is a "site" (i.e. a business layer in front of PG that can abstract away deficiencies in the query plan by adding e.g. caching.)
If you're directly working with PG, interactively writing OLAP reporting queries, you can have "working queries" that take ~300s (still practical enough to build your report) switch to taking milliseconds after an autovacuum (if, for example, a table being joined against was recently populated via ETL and hadn't been vacuumed yet.) People do notice that.