Index scan for PK constraint validation

[Philippe VIEGAS <p.viegas@xxxxxxxxxxxx>]

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.
But if we insert again in this table with a conflicting id, the primary key is being validated using the index as stated in our example :

ERROR: 23505: duplicate key value violates unique constraint "testpkidx_pkey"

DETAIL: Key (id)=(1) already exists.

SCHEMA NAME: public

TABLE NAME: testpkidx

CONSTRAINT NAME: testpkidx_pkey

LOCATION: _bt_check_unique, nbtinsert.c:664

But checking again the `pg_stat_user_indexes` view, the idx_scan attribute does not seem to be incremented.

Is the statistics of index usage for constraint validation being computed elsewhere ?

Are we missing something ?

Thanks in advance.

    Regards.

--

Philippe VIEGAS