Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6640F632483 for ; Mon, 11 Jan 2010 17:12:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50546-10 for ; Mon, 11 Jan 2010 21:12:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id 411736332A3 for ; Mon, 11 Jan 2010 17:12:07 -0400 (AST) Received: by bwz5 with SMTP id 5so14118790bwz.3 for ; Mon, 11 Jan 2010 13:12:05 -0800 (PST) MIME-Version: 1.0 Received: by 10.204.19.210 with SMTP id c18mr2124315bkb.73.1263244325188; Mon, 11 Jan 2010 13:12:05 -0800 (PST) In-Reply-To: <18803.1263241120@sss.pgh.pa.us> References: <3073cc9b0908191745o3dc63d95q6b9308ef5bd199f2@mail.gmail.com> <25315.1252881420@sss.pgh.pa.us> <3073cc9b1001111015t3888cbbex7171d61b952218da@mail.gmail.com> <603c8f071001111107v75276375g4d626dfc0efd8dc4@mail.gmail.com> <3073cc9b1001111114j7ddacd56p8966f9d61997a8b@mail.gmail.com> <3073cc9b1001111116x53427c9bm1e448128a3b4f6c6@mail.gmail.com> <18803.1263241120@sss.pgh.pa.us> From: Jaime Casanova Date: Mon, 11 Jan 2010 16:11:50 -0500 Message-ID: <3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com> Subject: Re: [PERFORMANCE] work_mem vs temp files issue To: Tom Lane Cc: Robert Haas , decibel , psql performance list Content-Type: multipart/mixed; boundary=00032555f2fa44775f047ce9fd8f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.589 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/219 X-Sequence-Number: 37099 --00032555f2fa44775f047ce9fd8f Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane wrote: > Jaime Casanova writes: >> LOG: =C2=A0begin tuple sort: nkeys =3D 1, workMem =3D 1024, randomAccess= =3D f >> LOG: =C2=A0switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u = sec elapsed 0.25 sec >> LOG: =C2=A0temporary file: path "base/pgsql_tmp/pgsql_tmp8507.5", size 4= 71010 [... some more temp files logged ...] >> LOG: =C2=A0internal sort ended, 118 KB used: CPU 0.10s/0.19u sec elapsed= 0.33 sec > > Hmm. =C2=A0Not clear where the temp files are coming from, but it's *not*= the > sort --- the "internal sort ended" line shows that that sort never went > to disk. =C2=A0What kind of plan is feeding the sort node? > i'm sure i have seen on disk sorts even when the files are small, but still i see a problem here... the temp files shoul be coming from hash operations but AFAICS the files are small and every hash operation should be using until work_mem memory, right? --=20 Atentamente, Jaime Casanova Soporte y capacitaci=C3=B3n de PostgreSQL Asesor=C3=ADa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 --00032555f2fa44775f047ce9fd8f Content-Type: text/plain; charset=US-ASCII; name="explain_sort.txt" Content-Disposition: attachment; filename="explain_sort.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g4br4up40 ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAKICAgICAgICAgICAgICAgICAKLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0KLS0tLS0tLS0tLS0tLS0tLS0KIExpbWl0ICAoY29zdD05MDkwLjYwLi45 MDkwLjY1IHJvd3M9MjAgd2lkdGg9MTY2KSAoYWN0dWFsIHRpbWU9NDQxLjYxOS4uNDQxLjY3NCBy b3dzPTIwIGxvb3BzPTEpCiAgIC0+ICBTb3J0ICAoY29zdD05MDg5Ljg1Li45MTA2LjI2IHJvd3M9 NjU2NyB3aWR0aD0xNjYpIChhY3R1YWwgdGltZT00NDEuMDYyLi40NDEuMzYyIHJvd3M9MzIwIGxv b3BzPTEpCiAgICAgICAgIFNvcnQgS2V5OiB0Y2YuZmVjaF9ob3JhCiAgICAgICAgIFNvcnQgTWV0 aG9kOiAgdG9wLU4gaGVhcHNvcnQgIE1lbW9yeTogMTE4a0IKICAgICAgICAgLT4gIEhhc2ggSm9p biAgKGNvc3Q9NjY2MC4xOC4uODc4My43NiByb3dzPTY1Njcgd2lkdGg9MTY2KSAoYWN0dWFsIHRp bWU9MzEwLjU2MS4uNDI3LjM5MyByb3dzPTY1MTEgbG9vcHM9MSkKICAgICAgICAgICAgICAgSGFz aCBDb25kOiAodGNzYy5pZF9zb2xpX2NvbXByYSA9IHRjZi5pZF9zb2xpX2NvbXByYSkKICAgICAg ICAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNvc3Q9NTYwMC40Ny4uNzU1MS45NyByb3dzPTY1NDgg d2lkdGg9MTU4KSAoYWN0dWFsIHRpbWU9MjgxLjI3OS4uMzc4LjQzNCByb3dzPTY1NDQgbG9vcHM9 MSkKICAgICAgICAgICAgICAgICAgICAgSGFzaCBDb25kOiAodGNzYy5lc3RhX3NvbGljaXR1ZCA9 IHRnZGMuc2VxX2RldF9jYXQpCiAgICAgICAgICAgICAgICAgICAgIC0+ICBIYXNoIEpvaW4gIChj b3N0PTU1NzcuODYuLjc0MzkuMzMgcm93cz02NTQ4IHdpZHRoPTE0NykgKGFjdHVhbCB0aW1lPTI3 OS41OTAuLjM2MS43MDQgcm93cz02NTQ1IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAg ICAgIEhhc2ggQ29uZDogKCh0Y3NjLnBlcnNvbmFfaWQpOjpudW1lcmljID0gdGdwLnBlcnNvbmFf aWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiB0Y29tX3NvbGlj aXR1ZF9jb21wcmEgdGNzYyAgKGNvc3Q9MC4wMC4uNzk5Ljc3IHJvd3M9NjU0OCB3aWR0aD0xMjUp IChhY3R1YWwgdGltZT0wLjAxMy4uMTguNDMzIHJvCndzPTY1NDUgbG9vcHM9MSkKICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAoKGVzdGFfc29saWNpdHVkIDw+IDQyNSkg QU5EIChlc3RhX3NvbGljaXR1ZCA8PiA0MjQpIEFORCAoZXN0YV9zb2xpY2l0dWQgPD4gNDE2KSkK ICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTM4NzguOTQuLjM4Nzgu OTQgcm93cz04MzU5NCB3aWR0aD0zNCkgKGFjdHVhbCB0aW1lPTI3OC44MzcuLjI3OC44Mzcgcm93 cz04MzU5NCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2Vx IFNjYW4gb24gdGdlbl9wZXJzb25hIHRncCAgKGNvc3Q9MC4wMC4uMzg3OC45NCByb3dzPTgzNTk0 IHdpZHRoPTM0KSAoYWN0dWFsIHRpbWU9MC4wMDYuLjEzNi41ODIgcm93cwo9ODM1OTQgbG9vcHM9 MSkKICAgICAgICAgICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTE0LjQ5Li4xNC40OSByb3dz PTY0OSB3aWR0aD0xNSkgKGFjdHVhbCB0aW1lPTEuNjU3Li4xLjY1NyByb3dzPTY0OSBsb29wcz0x KQogICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gdGdlbl9kZXRfY2F0 IHRnZGMgIChjb3N0PTAuMDAuLjE0LjQ5IHJvd3M9NjQ5IHdpZHRoPTE1KSAoYWN0dWFsIHRpbWU9 MC4wMDguLjAuODAzIHJvd3M9NjQ5IGxvb3BzPQoxKQogICAgICAgICAgICAgICAtPiAgSGFzaCAg KGNvc3Q9OTc3LjYyLi45NzcuNjIgcm93cz02NTY3IHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9Mjku MjY0Li4yOS4yNjQgcm93cz02NTU2IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgIC0+ICBT ZXEgU2NhbiBvbiB0Y29tX2ZlY2hhIHRjZiAgKGNvc3Q9MC4wMC4uOTc3LjYyIHJvd3M9NjU2NyB3 aWR0aD0xNikgKGFjdHVhbCB0aW1lPTAuMDE2Li4yMC41MzIgcm93cz02NTU2IGxvb3BzPTEpCiAg ICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKHNlcV9kZXRfY2F0ID0gMzg4KQogVG90 YWwgcnVudGltZTogNDQzLjQzNCBtcwooMjAgcm93cykK --00032555f2fa44775f047ce9fd8f--