Re: Small performance regression in 9.2 has a big impact

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux