Re: Patch: ResourceOwner optimization for tables with many partitions

From: Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru>
To: Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: ResourceOwner optimization for tables with many partitions
Date: 2016-01-21 09:26:44
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


I have applied this patch and can confirm ~10% speedup by this patch in presence of big amount of inherited tables.

Test case was as suggested by Aleksander: create 1000 inherited tables, no constraints, insert a row in each one, and issue single row queries over this table.

Xeon-based server 12C/24T, 50 connections, 30-min average:

TPS, no patch: 393 tps
TPS, with patch: 441 tps

The same setup but with single table with 1000 rows give performance about 188_000 tps, so overall speed of requests over a inherited table in this scenario is quite pathological (probably this is expected because database just execute 1000 selects to each inherited table). I've also tried to set range constraints for all inherited tables, so only one table was affected by query, but planning time increased a lot and total tps was again about 500 tps.

Also attaching two flame graphs measured during tests. It’s clearly visible that PortalDrop takes x4 less time after patch.


Attachment Content-Type Size
inher-patched.svg.gz application/x-gzip 93.9 KB
inher.svg.gz application/x-gzip 81.8 KB
unknown_filename text/plain 482 bytes

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2016-01-21 09:26:49 Re: [PoC] Asynchronous execution again (which is not parallel)
Previous Message Artur Zakirov 2016-01-21 09:15:45 Re: Fuzzy substring searching with the pg_trgm extension