pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix serious performance problems in json(b) to_tsvector(). 2+ messages / 1 participants [nested] [flat]
* pgsql: Fix serious performance problems in json(b) to_tsvector(). @ 2017-07-18 16:46 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2017-07-18 16:46 UTC (permalink / raw) To: pgsql-committers Fix serious performance problems in json(b) to_tsvector(). In an off-list followup to bug #14745, Bob Jones complained that to_tsvector() on a 2MB jsonb value took an unreasonable amount of time and space --- enough to draw the wrath of the OOM killer on his machine. On my machine, his example proved to require upwards of 18 seconds and 4GB, which seemed pretty bogus considering that to_tsvector() on the same data treated as text took just a couple hundred msec and 10 or so MB. On investigation, the problem is that the implementation scans each string element of the json(b) and converts it to tsvector separately, then applies tsvector_concat() to join those separate tsvectors. The unreasonable memory usage came from leaking every single one of the transient tsvectors --- but even without that mistake, this is an O(N^2) or worse algorithm, because tsvector_concat() has to repeatedly process the words coming from earlier elements. We can fix it by accumulating all the lexeme data and applying make_tsvector() just once. As a side benefit, that also makes the desired adjustment of lexeme positions far cheaper, because we can just tweak the running "pos" counter between JSON elements. In passing, try to make the explanation of that tweak more intelligible. (I didn't think that a barely-readable comment far removed from the actual code was helpful.) And do some minor other code beautification. Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/b4c6d31c0be0f5c42a75d50afcf13bdd392db4a1 Modified Files -------------- src/backend/tsearch/to_tsany.c | 120 ++++++++++++++++++++--------------------- src/include/tsearch/ts_type.h | 9 ---- 2 files changed, 58 insertions(+), 71 deletions(-) -- Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Fix serious performance problems in LZ4Stream_read_internal. @ 2025-10-13 17:17 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2025-10-13 17:17 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix serious performance problems in LZ4Stream_read_internal. I was distressed to find that reading an LZ4-compressed toc.dat file was hundreds of times slower than it ought to be. On investigation, the blame mostly affixes to LZ4Stream_read_overflow's habit of memmove'ing all the remaining buffered data after each read operation. Since reading a TOC file tends to involve a lot of small (even one-byte) decompression calls, that amounts to an O(N^2) cost. This could have been fixed with a minimal patch, but to my eyes LZ4Stream_read_internal and LZ4Stream_read_overflow are badly-written spaghetti code; in particular the eol_flag logic is inefficient and duplicative. I chose to throw the code away and rewrite from scratch. This version is about sixty lines shorter as well as not having the performance issue. Fortunately, AFAICT the only way to get to this problem is to manually LZ4-compress the toc.dat and/or blobs.toc files within a directory-style archive; in the main data files, we read blocks that are large enough that the O(N^2) behavior doesn't manifest. Few people do that, which likely explains the lack of field complaints. Otherwise this performance bug might be considered bad enough to warrant back-patching. Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Discussion: https://postgr.es/m/3515357.1760128017@sss.pgh.pa.us Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/1f8062dd9668572d66549fc798a7d2057aa34ee1 Modified Files -------------- src/bin/pg_dump/compress_lz4.c | 242 +++++++++++++++-------------------------- 1 file changed, 89 insertions(+), 153 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-13 17:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-07-18 16:46 pgsql: Fix serious performance problems in json(b) to_tsvector(). Tom Lane <tgl@sss.pgh.pa.us> 2025-10-13 17:17 pgsql: Fix serious performance problems in LZ4Stream_read_internal. Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox