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
Continue reading Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
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
Continue reading Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.
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
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".
Continue reading Explaining the unexplainable – part 5
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.
Continue reading Waiting for 9.2 – EXPLAIN TIMING
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
Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom
Continue reading Waiting for 9.2 – filtered info in explain analyze
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.
Continue reading Why is my index not being used?
Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:
ADD ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
Log message is rather terse, so what does it exactly do?
Continue reading Waiting for 8.5 – hinting for number of distinct values