Re: Small performance regression in 9.2 has a big impact

On Tue, Nov 25, 2014 at 8:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.