Re: BUG #17189: Index not created when primary key created - Mailing list pgsql-bugs
From | Pavel Stehule |
---|---|
Subject | Re: BUG #17189: Index not created when primary key created |
Date | |
Msg-id | CAFj8pRAaiQ=fy_9i3bwPSerDs9fSeX606izjbLg3G8zq3mLPqg@mail.gmail.com Whole thread Raw |
In response to | BUG #17189: Index not created when primary key created (PG Bug reporting form <noreply@postgresql.org>) |
List | pgsql-bugs |
Hi
po 13. 9. 2021 v 10:28 odesílatel PG Bug reporting form <noreply@postgresql.org> napsal:
The following bug has been logged on the website:
Bug reference: 17189
Logged by: Puneet Sharma
Email address: puneet.orcl@gmail.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)
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)
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)
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 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)
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
pgsql-bugs by date: