Thread: BUG #17189: Index not created when primary key created
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.
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
On Mon, Sep 13, 2021 at 05:27:00AM +0000, PG Bug reporting form wrote: > I have created the composite primary key which is created successfully but i > am not getting the index against the primary key. That's rather impossible. Please show create table statement, and output of `\d table_name` after creation. > 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. Index will be used only when it makes sense. If you'd do: select * from table; - that is select all rows - there is no point in using index, as it will be slower than getting seq scan. Plus - your table can be too small to effectively use index. Consider reading: https://www.depesz.com/2010/09/09/why-is-my-index-not-being-used/ Best regards, depesz
On Monday, September 13, 2021, PG Bug reporting form <noreply@postgresql.org> wrote:
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.
This isn’t a bug, it did create the indexes. The fact that a particular query choose not to use them is not evidence to the contrary.
David J.
Hi David,
Is it different from postgres 12 version and postgres 12.8 version.
When we are creating composite primary key default constraint has been created but not index like oracle.
Regards
Puneet kumar
On Mon, 13 Sep 2021 at 6:26 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, September 13, 2021, PG Bug reporting form <noreply@postgresql.org> wrote: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.This isn’t a bug, it did create the indexes. The fact that a particular query choose not to use them is not evidence to the contrary.David J.
Regards
Puneet Kumar
On Tue, Sep 21, 2021 at 11:34:37AM +0530, Puneet Sharma wrote:
> Hi David,
>
> Is it different from postgres 12 version and postgres 12.8 version.
>
> When we are creating composite primary key default constraint has been
> created but not index like oracle.
>
no, it's not different.
"""
postgres=# select version();
version
----------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
postgres=# create table t1(a int, b int, c int, primary key (a,b,c));
CREATE TABLE
postgres=# select indexrelid::regclass::text from pg_index
postgres-# where indrelid = 't1'::regclass and indisprimary;
indexrelid
------------
t1_pkey
(1 row)
"""
this has worked the right way for a long time, I would even say more
than 20 years. It's not suddenly failing.
As Hubert asked, please provide the output of `\d table_name`, or maybe
execute this query:
"""
select indexrelid::regclass::text from pg_index
where indrelid = 'table_name'::regclass
and indisprimary;
"""
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
Thanks a lot,
Ok, I will check.
On Tue, 21 Sep 2021 at 1:53 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote:
On Tue, Sep 21, 2021 at 11:34:37AM +0530, Puneet Sharma wrote:
> Hi David,
>
> Is it different from postgres 12 version and postgres 12.8 version.
>
> When we are creating composite primary key default constraint has been
> created but not index like oracle.
>
no, it's not different.
"""
postgres=# select version();
version
----------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
postgres=# create table t1(a int, b int, c int, primary key (a,b,c));
CREATE TABLE
postgres=# select indexrelid::regclass::text from pg_index
postgres-# where indrelid = 't1'::regclass and indisprimary;
indexrelid
------------
t1_pkey
(1 row)
"""
this has worked the right way for a long time, I would even say more
than 20 years. It's not suddenly failing.
As Hubert asked, please provide the output of `\d table_name`, or maybe
execute this query:
"""
select indexrelid::regclass::text from pg_index
where indrelid = 'table_name'::regclass
and indisprimary;
"""
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
Regards
Puneet Kumar