> 2016-11-02 15:57 GMT+01:00 Kevin Grittner <kgri...@gmail.com>: >> On Sun, Oct 30, 2016 at 10:35 AM, Kevin Grittner <kgri...@gmail.com> wrote: >> >>> SPI support would also >>> allow us to consider using set logic for validating foreign keys, >>> instead of the one-row-at-a-time approach currently used. >> >> Just as a proof of concept for this I used the attached test case >> to create foreign keys using current techniques versus set-oriented >> queries with the transition-tsr code. These probably can be >> improved, since this is a "first cut" off the top of my head. >> >> The delete of about one million rows from a "parent" table with no >> matching rows in the "child" table, and no index on referencing >> column in the child table, took 24:17.969 using current triggers >> and 00:03.262 using the set-based triggers. Yes, that reduces >> current run time for that case by 99.78%
On Wed, Nov 2, 2016 at 11:07 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > this is great number On Wed, Nov 2, 2016 at 11:41 AM, Adam Brusselback <adambrusselb...@gmail.com> wrote: > > That is really incredible. Gets rid of the need for an index on referencing > columns for a ton of use cases. Keep in mind that this is just a quick proof of concept. Unless all participating transactions are at serializable isolation level something would need to be done to handle race conditions, and that might affect performance. I do think that this approach is likely to be better in enough circumstances, even after that is covered, that it will be worth pursuing -- either as an option when declaring a foreign key, or as the only implementation. Until we have a version that covers the race conditions and benchmark it in a variety of workloads, it is hard to feel sure about the latter. There may be some situations where crawling the indexes a row at a time will perform better than this by enough to want to retain that option. A big plus of a single set-oriented statement is that it doesn't suck unlimited RAM -- it will use work_mem to limit each tuplestore and each query node, spilling to disk if needed. The current FK implementation sometimes runs for a very long time and can run people out of memory. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers