Search code examples
sqlpostgresqlindexingattributesquery-optimization

Parameters (attributes) of Postgres indexes (checking minima)

What is the attribute of the index "Checking minimums". How to disable the attribute. What does this attribute affect?Why is it on one index and not on the other

CREATE INDEX extendedreport_sparkid_w_idx ON extrep_mdm.extendedreport USING btree (sparkid) INCLUDE (inn, isacting) 
WHERE ((curr = 1) 
  AND ((okopf_codenew)::text !\~\* '755.*'::text) 
  AND (((okfs_code)::text = ANY ((ARRAY\['12'::character varying, '13'::character varying, '14'::character varying\])::text\[\])) OR ((okopf)::text \~* '.*Казенные.*|Государств.*|.Муницип.*|.*Бюджет.*'::text)) 
  AND ((okopf_codenew)::text !\~ '3000.*'::text) 
  AND ((fullnamerus)::text !\~* '.*НЕГОСУД.*'::text))


CREATE UNIQUE INDEX extr_sp_id_w_indx ON extrep_mdm.extendedreport USING btree (sparkid) INCLUDE (id) 
WHERE ((curr = 1) 
  AND ((isacting)::text \~\* '1|true'::text) 
  AND ((okopf_codenew)::text !\~\* '3000'::text))

check

unchecked

I tried to create in different ways, it does not work to remove this attribute when creating.

Solution

  • Checking the catalog view pg_index it seems that a column indcheckxmin (boolean) is shown on the screen.

    It's documented as

    If true, queries must not use the index until the xmin of this pg_index row is below their TransactionXmin event horizon, because the table may contain broken HOT chains with incompatible rows that they can see

    Similar question here may provide some hints how to resolve.