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.
i
? Are you at liberty to change the DB layout? – Erwin Brandstetter Aug 30 '17 at 3:18