From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Small performance regression in 9.2 has a big impact |
Date: | 2014-11-26 03:59:30 |
Message-ID: | 24669.1416974370@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
regards, tom lane
Attachment | Content-Type | Size |
---|---|---|
quick-performance-hacks.patch | text/x-diff | 7.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | M Tarkeshwar Rao | 2014-11-26 10:16:57 | issue in postgresql 9.1.3 in using arrow key in Solaris platform |
Previous Message | Tom Lane | 2014-11-25 23:02:22 | Re: Small performance regression in 9.2 has a big impact |