Date: Mon, 13 Aug 2018 15:39:44 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1534199984998-0.post@n3.nabble.com>
In-Reply-To: <20180806123050.1fab3011@firost>
References: 
 <CA+t6e1=uvR0PP_K8FtvMuebzrAofyzsTbMJoQB1pzimTBwfmOA@mail.gmail.com>
 <CAHw75vtyx-VHPY6tWQSYf52ofDG4D8pCZ6N3E_ZwS5yUmb88nQ@mail.gmail.com>
 <CA+t6e1=eJZotPr_cDh-b8PoYq3XXT3B1zh1-kyr_6AoH11GEkA@mail.gmail.com>
 <20180806100201.42a3c794@firost>
 <CA+t6e1=djbUK3XN8PshRETvO_9D92HWgqdf7iw=arRHZ7knbEw@mail.gmail.com>
 <20180806123050.1fab3011@firost>
Subject: Re: PostgreSQL 11 global index
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

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