Re: BUG #17189: Index not created when primary key created

Hi

po 13. 9. 2021 v 10:28 odesílatel PG Bug reporting form <
noreply(at)postgresql(dot)org> napsal:

> The following bug has been logged on the website:
>
> Bug reference: 17189
> Logged by: Puneet Sharma
> Email address: puneet(dot)orcl(at)gmail(dot)com
> PostgreSQL version: 12.0
> Operating system: Linux
> Description:
>
> I have created the composite primary key which is created successfully but
> i
> am not getting the index against the primary key.
>
> When i created a select query and includes the primary key column it is
> going with full table scan. Even i am creating an index on the columns it
> is
> also going with full table scan.
>

I cannot to reproduce it:

postgres=# create table foo(a int, b int, c int, primary key(a,b));
CREATE TABLE
postgres=# \d foo
Table "public.foo"
┌────────┬─────────┬───────────┬──────────┬─────────┐
│ Column │ Type │ Collation │ Nullable │ Default │
╞════════╪═════════╪═══════════╪══════════╪═════════╡
│ a │ integer │ │ not null │ │
│ b │ integer │ │ not null │ │
│ c │ integer │ │ │ │
└────────┴─────────┴───────────┴──────────┴─────────┘
Indexes:
"foo_pkey" PRIMARY KEY, btree (a, b)

Maybe Postgres doesn't use index, because table is too small and seq scan
is faster

postgres=# insert into foo values(10,20,30);
INSERT 0 1
postgres=# analyze foo;
ANALYZE
postgres=# explain select * from foo where a = 10 and b = 10;
┌────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..1.01 rows=1 width=12) │
│ Filter: ((a = 10) AND (b = 10)) │
└────────────────────────────────────────────────────┘
(2 rows)

postgres=# set enable_seqscan to off;
SET
postgres=# explain select * from foo where a = 10 and b = 10;
┌─────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════════════╡
│ Index Scan using foo_pkey on foo (cost=0.12..8.14 rows=1 width=12) │
│ Index Cond: ((a = 10) AND (b = 10)) │
└─────────────────────────────────────────────────────────────────────┘
(2 rows)

Regards

Pavel

postgres=# select version();
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version
│
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.1
20210728 (Red Hat 11.2.1-1), 64-bit │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

I am not sure, what is your composite primary key, but when I use composite
type, then it is working too

postgres=# create type ptt as (a int, b int);
CREATE TYPE
postgres=# create table foo2(id ptt primary key, b int);
CREATE TABLE
postgres=# \d foo2
Table "public.foo2"
┌────────┬─────────┬───────────┬──────────┬─────────┐
│ Column │ Type │ Collation │ Nullable │ Default │
╞════════╪═════════╪═══════════╪══════════╪═════════╡
│ id │ ptt │ │ not null │ │
│ b │ integer │ │ │ │
└────────┴─────────┴───────────┴──────────┴─────────┘
Indexes:
"foo2_pkey" PRIMARY KEY, btree (id)

postgres=# insert into foo2 values ((10,20), 30);
INSERT 0 1
postgres=# analyze foo;
ANALYZE
postgres=# set enable_seqscan to off;
SET
postgres=# explain select * from foo2 where id = (10,20);
┌───────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════╡
│ Index Scan using foo2_pkey on foo2 (cost=0.15..8.17 rows=1 width=36) │
│ Index Cond: (id = '(10,20)'::record) │
└───────────────────────────────────────────────────────────────────────┘
(2 rows)

Note

There is 8 bugfix releases for Postgres 12, please upgrade