Thanks a lot, it did help.
James
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, March 19, 2024 9:37 PM
To: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: cannot create index on partitioned table concurrently
On Tue, 2024-03-19 at 16:14 +0800, James Pang wrote:
> PGv14 , we migrated from Oracle to Postgresql, recently, we migrate
> index job from Oracle to PG. in Oracle, it's ok to create index online
> on partitioned table, but in Postgresql, ERROR: cannot create index
> on partitioned table concurrently. Instead, I go to create index
> concurrently on each child table separately.
> my question is, from SQL optimizer perspective, any difference
> between "create index on partitioned root table" and "create each
> index on child table directly", looks like optimizer still can use
> indexes with directly created on each child table.
Right.
But it is good to have the partitioned index to make sure that all partitions have an index.
See https://www.postgresql.org/docs/current/sql-createindex.html
"Concurrent builds for indexes on partitioned tables are currently
not supported. However, you may concurrently build the index on
each partition individually and then finally create the partitioned
index non-concurrently in order to reduce the time where writes to
the partitioned table will be locked out. In this case, building
the partitioned index is a metadata only operation."
Yours,
Laurenz Albe