Re: PostgreSQL 11 global index

Hello,

I was wondering if a simple trigger inserting ID values in a dedicated table
with an UNIQUE key
would have a chance to give similar performances than you trigger checking
ID with
SELECT count(1) > 1 FROM tabpart WHERE id = NEW.id

knowing that both triggers gives the same concurrency benefits ...

Find attached a little test case on my
PostgreSQL 11beta2 on x86_64-w64-mingw32, compiled by gcc.exe
(x86_64-win32-seh-rev1, Built by MinGW-W64 project) 7.2.0, 64-bit

inserting 100.000 lignes in a non partitionned table with a unique KEY takes
627 ms
inserting 100.000 lignes in a partitionned table (10 part) with "SELECT
COUNT" trigger takes 6696 ms
inserting 100.000 lignes in a partitionned table (10 part) with "INSERT ID
values" trigger takes 1634 ms

Is that ratio the same on a Linux box ?
I'm suspecting parsing time for "SELECT count(1) > 1 FROM tabpart WHERE id =
NEW.id",
is that possible ?

test_gbl_index.sql
<http://www.postgresql-archive.org/file/t348768/test_gbl_index.sql>

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html