IBM DB2
C:\>db2 create table public.tab1 (id int not null, col1 int not null)
C:\>db2 create unique index tab1_ix on public.tab1 (id) include (col1)
C:\>db2 alter table public.tab1 add primary key (id)
SQL0598W Existing index "IN1139.TAB1_IX" is used as the index for the primary
key or a unique key. SQLSTATE=01550
It is just info, that existing index was used.
POSTGRESQL
db=# create table public.tab1 (id int not null, col1 int not null);
db=# create unique index tab1_ix on public.tab1 (id) include (col1);
db=# alter table public.tab1 add primary key (id);
db=# \d public.tab1;
Table "public.tab1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
col1 | integer | | not null |
Indexes:
"tab1_pkey" PRIMARY KEY, btree (id)
"tab1_ix" UNIQUE, btree (id) INCLUDE (col1)
PostgreSQL created additional index for primary key.
Why is this important? a) In many cases in explain I have seen if primary key is created then database uses primary key index for queries. But if I drop primary key then unique index and queries run way faster (because of Index Only Scan). b) Additional disk space is used for additional primary key index. c) Every update/insert/delete and two indexes must be updated instead of one.
Questions:
- Is there a way I can force PostgreSQL database to use existing index, when primary key is added on table with existing unique index (like in IBM Db2)?
- Is there a way I can force PostgreSQL to use unique index (and so use Index Only Scan) instead of primary key index (and using only Index Scan) when queries are executed?
