We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases.
Continue reading Case study: optimization of weirdly picked bad plan
Tag: analyze
Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN.
On 3rd of April 2024, Tom Lane committed patch:
Invent SERIALIZE option for EXPLAIN. EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about the volume of data emitted by a query, as well as the time taken to convert the data to the on-the-wire format. Previously there was no way to investigate this without actually sending the data to the client, in which case network transmission costs might swamp what you wanted to see. In particular this feature allows investigating the costs of de-TOASTing compressed or out-of-line data during formatting. Stepan Rutz and Matthias van de Meent, reviewed by Tomas Vondra and myself Discussion: https://postgr.es/m/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de
Continue reading Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN.
Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
On 14th of December 2022, Jeff Davis committed patch:
Add grantable MAINTAIN privilege and pg_maintain role. Allows VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE. Effectively reverts 4441fc704d. Instead of creating separate privileges for VACUUM, ANALYZE, and other maintenance commands, group them together under a single MAINTAIN privilege. Author: Nathan Bossart Discussion: https://postgr.es/m/20221212210136.GA449764@nathanxps13 Discussion: https://postgr.es/m/45224.1670476523@sss.pgh.pa.us
Continue reading Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.
On 20th of January 2020, Amit Kapila committed patch:
Allow vacuum command to process indexes in parallel. This feature allows the vacuum to leverage multiple CPUs in order to process indexes. This enables us to perform index vacuuming and index cleanup with background workers. This adds a PARALLEL option to VACUUM command where the user can specify the number of workers that can be used to perform the command which is limited by the number of indexes on a table. Specifying zero as a number of workers will disable parallelism. This option can't be used with the FULL option. Each index is processed by at most one vacuum process. Therefore parallel vacuum can be used when the table has at least two indexes. The parallel degree is either specified by the user or determined based on the number of indexes that the table has, and further limited by max_parallel_maintenance_workers. The index can participate in parallel vacuum iff it's size is greater than min_parallel_index_scan_size. Author: Masahiko Sawada and Amit Kapila Mahendra Singh and Sergei Kornilov Discussion: https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com
Continue reading Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.
Waiting for 9.4 – auto_explain: Add logging of trigger execution
On 4th of March, Alvaro Herrera committed patch:
auto_explain: Add logging of trigger execution Author: Kyotaro HORIGUCHI Reviewed-by: Jaime Casanova
Continue reading Waiting for 9.4 – auto_explain: Add logging of trigger execution
Explaining the unexplainable – part 5
In previous posts in this series, I talked about how to read EXPLAIN output, and what each line (operation/node) means.
Now, in the final post, I will try to explain how it happens that Pg chooses “Operation X" over “Operation Y".
Waiting for 9.2 – EXPLAIN TIMING
On 7th of February, Robert Haas committed patch:
Sometimes it may be useful to get actual row counts out of EXPLAIN (ANALYZE) without paying the cost of timing every node entry/exit. With this patch, you can say EXPLAIN (ANALYZE, TIMING OFF) to get that. Tomas Vondra, reviewed by Eric Theise, with minor doc changes by me.
Waiting for 9.2 – filtered info in explain analyze
On 22nd of September, Tom Lane committed patch:
Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps. This provides information about the numbers of tuples that were visited but not returned by table scans, as well as the numbers of join tuples that were considered and discarded within a join plan node. There is still some discussion going on about the best way to report counts for outer-join situations, but I think most of what's in the patch would not change if we revise that, so I'm going to go ahead and commit it as-is. Documentation changes to follow (they weren't in the submitted patch either). Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom
Continue reading Waiting for 9.2 – filtered info in explain analyze
Why is my index not being used?
This question (and its variants) show quite often on #postgresql on IRC. People get sequential scans, and are worried that it's slow and bad.
So, I hope that this blogpost will shed some light on the subject why indexes are being chosen to be used, or not.
Waiting for 8.5 – hinting for number of distinct values
Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:
Log Message: ----------- ADD ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT Robert Haas
Log message is rather terse, so what does it exactly do?
Continue reading Waiting for 8.5 – hinting for number of distinct values