Re: Index scan for PK constraint validation

On Wednesday, February 8, 2023, Philippe VIEGAS <p(dot)viegas(at)loxodata(dot)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.