X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EFA14D98E5 for ; Sun, 2 Oct 2005 09:32:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56700-10 for ; Sun, 2 Oct 2005 12:32:49 +0000 (GMT) Received: from svana.org (svana.org [203.20.62.76]) by svr1.postgresql.org (Postfix) with ESMTP id 98FA2D98C9 for ; Sun, 2 Oct 2005 09:32:50 -0300 (ADT) Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian)) id 1EM31J-0008PT-00; Sun, 02 Oct 2005 22:32:45 +1000 Date: Sun, 2 Oct 2005 14:32:45 +0200 From: Martijn van Oosterhout To: Tom Lane Cc: Ron Peacetree , pgsql-hackers@postgresql.org Subject: Re: [PERFORM] A Better External Sort? Message-ID: <20051002123240.GA30492@svana.org> Reply-To: Martijn van Oosterhout References: <18329659.1128188552696.JavaMail.root@elwamui-polski.atl.sa.earthlink.net> <20051001215602.GG13830@svana.org> <29837.1128223567@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="SUOF0GtieIMvvwua" Content-Disposition: inline In-Reply-To: <29837.1128223567@sss.pgh.pa.us> User-Agent: Mutt/1.3.28i X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6 X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522 48A7 6394 F08A 0DC6 7BE6 X-PGP-Key-URL: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 required=5 tests=[AWL=0.008] X-Spam-Level: X-Archive-Number: 200510/63 X-Sequence-Number: 73922 --SUOF0GtieIMvvwua Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sat, Oct 01, 2005 at 11:26:07PM -0400, Tom Lane wrote: > Martijn van Oosterhout writes: > > Anyway, to bring some real info I just profiled PostgreSQL 8.1beta > > doing an index create on a 2960296 row table (3 columns, table size > > 317MB). >=20 > 3 columns in the index you mean? What were the column datatypes? > Any null values? Nope, three columns in the table, one column in the index, no nulls. The indexed column was integer. I did it once with around 6500 values repeated over and over, lots of duplicate kays. And once on a serial column but it made no descernable difference either way. Although the comparison function was called less (only 76 million times), presumably because it was mostly sorted already. > > The number 1 bottleneck with 41% of user time is comparetup_index. > > ... > > The thing is, I can't see anything in comparetup_index() that could > > take much time. >=20 > The index_getattr and heap_getattr macros can be annoyingly expensive. And yet they are optimised for the common case. nocache_index_getattr was only called 7 times, which is about what you expect. I'm getting annotated output now, to determine which line takes the time... Actually, my previous profile overstated stuff a bit. Profiling turned off optimisation so I put it back and you get better results but the order doesn't change much. By line results are below. The top two are the index_getattr calls in comparetup_index. Third and fourth are the HEAPCOMPARES in tuplesort_heap_siftup. Then comes the inlineApplySortFunction call (which isn't being inlined, despite suggesting it should be, -Winline warns about this). Looks to me that there are no real gains to be made in this function. What is needed is an algorithmic change to call this function less often... Have a nice weekend, % cumulative self self total =20 time seconds seconds calls ms/call ms/call name =20 9.40 22.56 22.56 comparetup_index (tup= lesort.c:2042 @ 8251060) 5.07 34.73 12.17 comparetup_index (tup= lesort.c:2043 @ 82510c0) 4.73 46.09 11.36 tuplesort_heap_siftup= (tuplesort.c:1648 @ 825074d) 3.48 54.45 8.36 tuplesort_heap_siftup= (tuplesort.c:1661 @ 82507a9) 2.80 61.18 6.73 comparetup_index (tup= lesort.c:2102 @ 8251201) 2.68 67.62 6.44 comparetup_index (tup= lesort.c:2048 @ 8251120) 2.16 72.82 5.20 tuplesort_heap_siftup= (tuplesort.c:1652 @ 825076d) 1.88 77.34 4.52 76025782 0.00 0.00 comparetup_index (tup= lesort.c:2016 @ 8251010) 1.82 81.70 4.36 76025782 0.00 0.00 inlineApplySortFuncti= on (tuplesort.c:1833 @ 8251800) 1.73 85.85 4.15 readtup_heap (tupleso= rt.c:2000 @ 8250fd8) 1.67 89.86 4.01 AllocSetAlloc (aset.c= :568 @ 824bec0) 1.61 93.72 3.86 comparetup_index (tup= lesort.c:2025 @ 825102f) 1.47 97.25 3.53 76025785 0.00 0.00 btint4cmp (nbtcompare= .c:74 @ 80924a0) 1.11 99.92 2.67 readtup_datum (tuples= ort.c:2224 @ 82517c4) 1.10 102.55 2.64 comparetup_index (tup= lesort.c:2103 @ 82511e7) % cumulative self self total =20 time seconds seconds calls s/call s/call name =20 28.34 68.01 68.01 76025782 0.00 0.00 comparetup_index 13.56 100.54 32.53 7148934 0.00 0.00 tuplesort_heap_siftup 8.66 121.33 20.79 76025782 0.00 0.00 inlineApplySortFuncti= on 4.43 131.96 10.63 13084567 0.00 0.00 AllocSetAlloc 3.73 140.90 8.94 76025785 0.00 0.00 btint4cmp 2.15 146.07 5.17 6095625 0.00 0.00 LWLockAcquire 2.02 150.92 4.85 2960305 0.00 0.00 heapgettup 1.98 155.66 4.74 7148934 0.00 0.00 tuplesort_heap_insert 1.78 159.94 4.28 2960312 0.00 0.00 slot_deform_tuple 1.73 164.09 4.15 readtup_heap 1.67 168.09 4.00 6095642 0.00 0.00 LWLockRelease 1.53 171.76 3.68 2960308 0.00 0.00 index_form_tuple 1.44 175.21 3.45 13083442 0.00 0.00 AllocSetFree 1.28 178.28 3.07 8377285 0.00 0.00 LogicalTapeWrite 1.25 181.29 3.01 8377285 0.00 0.00 LogicalTapeRead 1.11 183.96 2.67 readtup_datum 1.06 186.51 2.55 1 2.55 123.54 IndexBuildHeapScan --=20 Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. --SUOF0GtieIMvvwua Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQFDP9NnIB7bNG8LQkwRAh0TAJ0UnvSw9wBY+EdineD6QSWRUGh2EwCfVwq1 GVyJz5jgohzQzP6inojA9G4= =s43K -----END PGP SIGNATURE----- --SUOF0GtieIMvvwua--