On Tue, Nov 25, 2014 at 8:59 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > I wrote: >>> Hmm, I don't like the trend here. For the repeat-1000x query, I get >>> these reported execution times: > >>> 8.4 360 ms >>> 9.0 365 ms >>> 9.1 440 ms >>> 9.2 510 ms >>> 9.3 550 ms >>> 9.4 570 ms >>> head 570 ms > >> I made a quick-hack patch to suppress redundant GetDefaultOpclass calls >> in typcache.c, and found that that brought HEAD's runtime down to 460ms. > > I found some additional low-hanging fruit by comparing gprof call counts > in 8.4 and HEAD: > > * OverrideSearchPathMatchesCurrent(), which is not there at all in 8.4 > or 9.2, accounts for a depressingly large amount of palloc/pfree traffic. > The implementation was quick-n-dirty to begin with, but workloads > like this one call it often enough to make it a pain point. > > * plpgsql's setup_param_list() contributes another large fraction of > added palloc/pfree traffic; this is evidently caused by the temporary > bitmapset needed for its bms_first_member() loop, which was not there > in 8.4 but is there in 9.2. > > I've been able to bring HEAD's runtime down to about 415 ms with the > collection of more-or-less quick hacks attached. None of them are > ready to commit but I thought I'd post them for the record. > > After review of all this, I think the aspect of your example that is > causing performance issues is that there are a lot of non-inline-able > SQL-language function calls. That's not a case that we've put much > thought into lately. I doubt we are going to get all the way back to > where 8.4 was in the short term, because I can see that there is a > significant amount of new computation associated with collation > management during parsing (catcache lookups driven by get_typcollation, > assign_collations_walker, etc). The long-term answer to that is to > improve the SQL-language function support so that it can cache the results > of parsing the function body; we have surely got enough plancache support > for that now, but no one's attempted to apply it in functions.c. Thanks so much for the work on this. We won't be applying a patch in prod but we can definitely get a feel for the change on some test boxes. And if I didn't say it, Thanks to Heikki for his advice. Huge difference there too. This is exactly why I love using Postgres so much. The community support. No other software package has this kind of support. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
