brininsert optimization opportunity
Soumyadeep Chakraborty <soumyadeep2007@gmail.com>
From: Soumyadeep Chakraborty <soumyadeep2007@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Ashwin Agrawal <ashwinstar@gmail.com>,
Tomas Vondra <tomas.vondra@enterprisedb.com>
Date: 2023-07-03T22:21:58Z
Lists: pgsql-hackers
Attachments
- perf_diff.out (application/octet-stream)
- v1-0001-Reuse-revmap-and-brin-desc-in-brininsert.patch (text/x-patch) patch v1-0001
Hello hackers, My colleague, Ashwin, pointed out to me that brininsert's per-tuple init of the revmap access struct can have non-trivial overhead. Turns out he is right. We are saving 24 bytes of memory per-call for the access struct, and a bit on buffer/locking overhead, with the attached patch. The implementation ties the revmap cleanup as a MemoryContext callback to the IndexInfo struct's MemoryContext, as there is no teardown function provided by the index AM for end-of-insert-command. Test setup (local Ubuntu workstation): # Drop caches and restart between each run: sudo sh -c "sync; echo 3 > /proc/sys/vm/drop_caches;" pg_ctl -D /usr/local/pgsql/data/ -l /tmp/logfile restart \timing DROP TABLE heap; CREATE TABLE heap(i int); CREATE INDEX ON heap USING brin(i) WITH (pages_per_range=1); INSERT INTO heap SELECT 1 FROM generate_series(1, 200000000); Results: We see an improvement for 100M tuples and an even bigger improvement for 200M tuples. Master (29cf61ade3f245aa40f427a1d6345287ef77e622): test=# INSERT INTO heap SELECT 1 FROM generate_series(1, 100000000); INSERT 0 100000000 Time: 222762.159 ms (03:42.762) -- 3 runs test=# INSERT INTO heap SELECT 1 FROM generate_series(1, 200000000); INSERT 0 200000000 Time: 471168.181 ms (07:51.168) Time: 457071.883 ms (07:37.072) TimeL 486969.205 ms (08:06.969) Branch: test2=# INSERT INTO heap SELECT 1 FROM generate_series(1, 100000000); INSERT 0 100000000 Time: 200046.519 ms (03:20.047) -- 3 runs test2=# INSERT INTO heap SELECT 1 FROM generate_series(1, 200000000); INSERT 0 200000000 Time: 369041.832 ms (06:09.042) Time: 365483.382 ms (06:05.483) Time: 375506.144 ms (06:15.506) # Profiled backend running INSERT of 100000000 rows sudo perf record -p 11951 --call-graph fp sleep 180 Please see attached perf diff between master and branch. We see that we save on a bit of overhead from brinRevmapInitialize(), brinRevmapTerminate() and lock routines. Regards, Soumyadeep (VMware)
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add missing index_insert_cleanup calls
- 41d2c6f952ed 17.0 landed
-
Fix a couple typos in BRIN code
- 95d14b7ae26d 17.0 landed
-
Check if ii_AmCache is NULL in aminsertcleanup
- a82ee7ef3aac 17.0 landed
-
Use fipshash in brin_multi test
- 86b64bafc19c 17.0 cited
-
Reuse BrinDesc and BrinRevmap in brininsert
- c1ec02be1d79 17.0 landed
-
Consider fillfactor when estimating relation size
- 29cf61ade3f2 17.0 cited
-
Postpone some stuff out of ExecInitModifyTable.
- c5b7ba4e67ae 14.0 cited