Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHj6p-0005h3-Rp for pgsql-admin@arkaria.postgresql.org; Wed, 31 Oct 2018 05:32:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gHj4g-0007Ud-LT for pgsql-admin@arkaria.postgresql.org; Wed, 31 Oct 2018 05:29:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHj4g-0007UW-2y for pgsql-admin@lists.postgresql.org; Wed, 31 Oct 2018 05:29:50 +0000 Received: from sonic315-42.consmr.mail.bf2.yahoo.com ([74.6.134.216]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHj4c-0005Gr-Mq for pgsql-admin@postgresql.org; Wed, 31 Oct 2018 05:29:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.de; s=s2048; t=1540963783; bh=eH3mvwjBxt2NpN8aSD0YOBAf/IOranLplzP0xm+bRH8=; h=Date:From:To:Subject:References:From:Subject; b=tySvBf3Pa0WJ/Tv99aRqgHokfFn0RBW+ymKvePtZ0+4YgLI9xf39xjih1tSnk3TUidfWOGqQC6I+gM46Nh9HQXor1Il+Pnw/YWDYFMChIEJl72UA+Ta9H14CQzyqc7nhS9fHjPlDxNDO9xCBueNYtrYhOz92CwUts2DZRATz8X/8TcYucqv1dKXZGRzJjlXx5hXlxF5SbxoylUYjeYuwJ86JfGHZVCe1A7TUWfCM4KTv4iq7mdwq0zDnMPscWhpFBbCUgB+j8Va+t0vH0bSh4hxc+UVkQ/9jKmzltCT6tSqANWgLQwdpub0LvC+3iMza7cqcAg0YUTXF1WFBv3Gqbg== X-YMail-OSG: 8yXLRL8VM1lCGqDwCE4oVMUe.akhY8Murvw2AiyFfaPVPglZyCXzzN3fdy4VqfN 3rCXYi9O.A.CCmeGfDPonvVJYA7agx4S4LtSPBCJdhmb.5e7DI6.IrqKBogcMrhC9VKoQZYDmeAn KS2CAJpFYzUCxlpnpqgqVJK1rVx2gpGPet_ISMVJbfeWHP3mvMQ6yf5rN7jTiDFzZDn04wGHY2iq LXIekLfPuNCahcqkR3UZ6mDiAa8NPcYV.ONF5aKabfSEAei7zHIoQhMb1iMXYGSeHGIaE9Zlnqr9 qTdT7L6YoNPwA8uPXdq7yyXWBx_42Qb38xIUwg2Bxuo8kTznt9iyZP5uVRxNYTmMpfmuxiP8qD72 nMgCE5zsBmR40.nG03JnlxKF_xBTww1mjFEbVNNBEo1DOHBiW2RBjEwty4y8Icr68r4Zz0p_Oq19 6fbjLgtwDJ4xl_mkvACIVxyVYWn4dDevRNe96TsAiHAyxOeeU9sVcSC1Wtsi0s46TlCFHSPj5tEa I5oujKrDaqDbJGaAbJ5x351o5ht_K3pYCubLOJhZxG2zihLaEkcI7E7YStxqKjvILO0pgKJhcCGq VFuqCKgnLliQLWhH16ZEFcWKW.0rNm8okoXZf_aqWC8e_D_CRX8xG72tsFsoGm4_KboVuQg6GmdX LFpCPRh8Ft.V50shLE.C3UiLchaxnIybjlS9sCTKo060A4zms7adt6hDCuwtsqSENyfU.1T9LzCs DoPTzgcZOqTiKpXwQANcKy0AdcpyfZvjpqCHGbwv4NM8zvtlu5b9TmEknxQyMPDWB2w11OKrS05f r1EUOb6ZjPu_eHjYwQSYKA6sejRzYA7TwEjZv06CXVws7b0TwmyTbg8BnByz1YOu5AtrJG8OH4Y5 wRmKZ4Jsv0VbkXyAH5PiZ.6mJvZXyAlCVz5VHguBXcF1TaievtuFUjWmzkPm4TWckPAyVbLcVl9y M0u6Ov9xs6ErKCYlPcwH5LkmS Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.bf2.yahoo.com with HTTP; Wed, 31 Oct 2018 05:29:43 +0000 Date: Wed, 31 Oct 2018 05:25:34 +0000 (UTC) From: Simon Liesenfeld To: "pgsql-admin@postgresql.org" Message-ID: <1102548183.19607699.1540963534074@mail.yahoo.com> Subject: Poverty Partitioning, include indexes MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19607698_1964706844.1540963534071" References: <1102548183.19607699.1540963534074.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12512 YMailNorrin Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:63.0) Gecko/20100101 Firefox/63.0 Content-Length: 9259 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_19607698_1964706844.1540963534071 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi all, after decades I had a look at Postgres again, last week, due to V11.Quite i= mpressive, what has been achieved by the Postgres team since then.Especiall= y the Performance of recursive=C2=A0 CTEs is superb,especially in combinati= on with window functions, lag, lead, row_number Before Diving into the new partitioning methods, of which I assume, that=C2= =A0 they imply constraints on the used data model,I observed, that a=C2=A0 = table is a file, e.g. data/base/1234/5678 .=C2=A0 As soon the files size ex= ceeds1 Gig, more files will be created, 5678.1 for the next Gig, 5678.2 for= another , and=C2=A0 so on.I had 10 of them for my CTE test table. I brought down the pg-server, moved the ones with odd extension number to a= nother=C2=A0 disk=C2=A0 and created=20 symbolic links in=C2=A0 data/base/1234/ pointing to the moved files. I restarted the pg-server, I was surprised, that everything still worked we= ll,=C2=A0 and I think, I noticed some speedup of my query. I assume, that i= s, because pg gathers the data of the files in parallel, and two disk can s= huffle more data in the same time than one disk. Is this a recommendable practice? Is it supported by postgres to do this by= CREATE TABLE, Alter table?Can somebody confirm the performance gain, does = it even improve=C2=A0 with more disks? Oh. I spotted another highlight in V11, The Include indexes.Tables with ple= nty of attributes actually speedup with ratio=20 number of all attributes / number of selected attributes=20 if an include index is build on the selected attributes + the PK.with small= index files and no impact on the Data model. ------=_Part_19607698_1964706844.1540963534071 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi all,

after decades I had a = look at Postgres again, last week, due to V11.
Quite impressive, what has been achieved by the= Postgres team since then.
Especially the Performance of recursive  CTEs is superb,
=
especially in comb= ination with window functions, lag, lead, row_number

Before Diving into the new partitioning meth= ods, of which I assume, that  they imply constraints on the used data = model,
I observed, = that a  table is a file, e.g. data/base/1234/5678 .  As soon the = files size exceeds
= 1 Gig, more files will be created, 5678.1 for the next Gig, 567= 8.2 for another , and  so on.
I had 10 of them for my CTE test = table.

I brought down the pg-server, moved the ones wit= h odd extension number to another  disk  and created
<= /span>
= symbolic links in  data/base/1234/ pointing to the moved files.<= br>
I restarted the pg-server, I was surprised, that ev= erything still worked well,  and I think, I noticed some speedup of my= query. I assume, that is, because pg gathers the data of the files in para= llel, and two disk can shuffle more data in the same time than one disk.
=
Is this a recommendable practice= ? Is it supported by postgres to do this by CREATE TABLE, Alter table?
Can somebody confirm the performance gain, does it even imp= rove  with more disks?

Oh. I spotted another highlight in V11, The Include indexes.
Tables with plenty of attributes actually speedup with ratio
number of all attributes / number of selected attribut= es
if an include index is build on the selected attributes + the PK.
with small index files and no impact on the Data model.
<= span>


<= span>


<= span>



------=_Part_19607698_1964706844.1540963534071--