Thanks a lot, it did help. James -----Original Message----- From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx> Sent: Tuesday, March 19, 2024 9:37 PM To: James Pang <jamespang886@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx 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
