On Wednesday, February 8, 2023, Philippe VIEGAS <
p.viegas@loxodata.com> wrote:
Hi all,
I was wondering why the index statistics usage were not reflecting the index usage for primary key constraint validation ?
When we create a table with a primary key, PostgreSQL creates a Btree index for the validation of this constraint : index is defined unique (indisunique=t) and primary (indisprimary=t) from the pg_index and pg_class catalogs.
Inserting data on the above created table and selecting based on the primary key column will increment the idx_scan from `pg_stat_user_indexes` view.
The select produces the index scan, not the insert. The insert doesn’t scan, it populates and aborts if that population fails. It doesn’t check first.
David J.