I have a table essentially equivalent to this example:
create table my_table(i integer, x text, y integer, z integer); create index idx_i_x on my_table(i, x); create index idx_i_y on my_table(i, y); create index idx_i_z on my_table(i, z); create index idx_i_x on my_table(i, x, y, z);
All queries have column
i in the where clause.
It seems that this table is a good candidate to benefit from table clustering (https://www.postgresql.org/docs/9.6/static/sql-cluster.html)
Stats on my_table: for each value of
i, there will be from 5 to 10 million rows.
Updates on the table follow the pattern:
update my_table set i = 4 where i = 0; delete from my_table where i = 6;
All updates or deletes are "bulk", i.e. they involve all rows for a given value of i.
Given what is written in the docs (https://www.postgresql.org/docs/9.6/static/sql-cluster.html) :
Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered.
it seems as if it doesn't matter that rows are not reclustered for updates of i, since when I execute this update (for example) :
update my_table set i = 4 where i = 0;
all rows that had
i = 0 are still clustered together.