pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17189: Index not created when primary key created
7+ messages / 6 participants
[nested] [flat]
* BUG #17189: Index not created when primary key created
@ 2021-09-13 05:27 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 3 replies; 7+ messages in thread
From: PG Bug reporting form @ 2021-09-13 05:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: puneet.orcl@gmail.com
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-13 09:24 Pavel Stehule <pavel.stehule@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2021-09-13 09:24 UTC (permalink / raw)
To: puneet.orcl@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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)
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-13 10:36 hubert depesz lubaczewski <depesz@depesz.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 7+ messages in thread
From: hubert depesz lubaczewski @ 2021-09-13 10:36 UTC (permalink / raw)
To: puneet.orcl@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-13 12:56 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2021-09-13 12:56 UTC (permalink / raw)
To: puneet.orcl@gmail.com <puneet.orcl@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-21 06:04 Puneet Sharma <puneet.orcl@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Puneet Sharma @ 2021-09-21 06:04 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-21 08:23 Jaime Casanova <jcasanov@systemguards.com.ec>
parent: Puneet Sharma <puneet.orcl@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jaime Casanova @ 2021-09-21 08:23 UTC (permalink / raw)
To: Puneet Sharma <puneet.orcl@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17189: Index not created when primary key created
@ 2021-09-21 10:49 Puneet Sharma <puneet.orcl@gmail.com>
parent: Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 0 replies; 7+ messages in thread
From: Puneet Sharma @ 2021-09-21 10:49 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-09-21 10:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-13 05:27 BUG #17189: Index not created when primary key created PG Bug reporting form <noreply@postgresql.org>
2021-09-13 09:24 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-09-13 10:36 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-09-13 12:56 ` David G. Johnston <david.g.johnston@gmail.com>
2021-09-21 06:04 ` Puneet Sharma <puneet.orcl@gmail.com>
2021-09-21 08:23 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2021-09-21 10:49 ` Puneet Sharma <puneet.orcl@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox