From: | Philippe VIEGAS <p(dot)viegas(at)loxodata(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Index scan for PK constraint validation |
Date: | 2023-02-08 12:09:51 |
Message-ID: | 337ea717-3d64-6b72-1243-770ae2ff9a04@loxodata.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 keyvalueviolates uniqueconstraint"testpkidx_pkey"
DETAIL: Key(id)=(1) already exists.
SCHEMANAME: public
TABLENAME: testpkidx
CONSTRAINTNAME: 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sebastien Flaesch | 2023-02-08 13:00:21 | Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column |
Previous Message | Peter J. Holzer | 2023-02-08 11:33:33 | Re: Sequence vs UUID |