Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D2A1847679B for ; Tue, 1 Oct 2002 08:18:27 -0400 (EDT) Received: from odysseus2.dohle.com (odysseus2.dohle.com [194.127.185.20]) by postgresql.org (Postfix) with ESMTP id 80F4B47677D for ; Tue, 1 Oct 2002 08:18:26 -0400 (EDT) Received: from majestix.dohle.com (majestix.dohle.com [194.127.190.1]) by odysseus2.dohle.com (8.9.3/8.9.3) with ESMTP id OAA25952 for ; Tue, 1 Oct 2002 14:18:16 +0200 Received: (from cyrus@localhost) by majestix.dohle.com (8.11.1/8.10.2) id g91CIG926848 for pgsql-sql@postgresql.org.vscan; Tue, 1 Oct 2002 14:18:16 +0200 Received: from wcontzen (wcontzen.dohle.com [172.20.4.43]) by majestix.dohle.com (8.11.1/8.10.2) with SMTP id g91CHs014511 for ; Tue, 1 Oct 2002 14:17:54 +0200 From: "Michael Contzen" To: Subject: How slow is distinct - 2nd Date: Tue, 1 Oct 2002 14:18:50 +0200 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0029_01C26955.76B204F0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-MS-TNEF-Correlator: X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/52 X-Sequence-Number: 9770 This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C26955.76B204F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hello, I posted some observations to the performance of postgres some weeks ago. The problem with the poor performance of "select distinct" still exists, but I tried to worked out some reproducable results in a less complicated way than in my first postings. 'select distinct' preforms on Oracle about 30 times faster than in Postgres. Well, only 'select distinct' is slow. All other parts of our application using Postgres perform quite well on Postgres (with really big amounts of data). (All tests on Postgres 7.3b1 and Oracle 9.0.1.3.0. Hardware: 4 * 1800 MHz Xeon, 3 * 100GB IDE Software-Raid0) Suse-Linux, 2.4.18 SMP-kernel) Here the table: mc=# \d egal Table "public.egal" Column | Type | Modifiers --------+---------+----------- i | integer | mc=# select count(*) from egal; count --------- 7227744 (1 row) mc=# select count(distinct i) from egal; count ------- 67 (1 row) (The integers have values between 0 and 99 are an extract of our warehouse and nearly distributed randomly) The last query - count(distinct) - takes 1m30s while oracle needs 0m7s for the same query and data. Getting the distinct rows and measuring the time results to time echo "select distinct i from egal;"|psql >/dev/null real 4m52.108s user 0m0.000s sys 0m0.010s Here I don't understand the difference in performance between "select distinct i" and "select count(distinct i)" - Oracle takes constantly 7s for each query: time echo "select distinct i from egal;"|sqlplus xxxx/yyyy >/dev/null real 0m6.979s user 0m0.020s sys 0m0.030s In the first case (count(distinct)) postgres produces a temporary file of 86 MB in pgsql_tmp, in the second case (select distinct) the temp-file increases to 260 MB. (this is even larger than the table size of egal which is 232 MB) I think the planner has not many choices for this query so it results to mc=# explain select distinct (i) from egal; QUERY PLAN --------------------------------------------------------------------------- Unique (cost=1292118.29..1328257.01 rows=722774 width=4) -> Sort (cost=1292118.29..1310187.65 rows=7227744 width=4) Sort Key: i -> Seq Scan on egal (cost=0.00..104118.44 rows=7227744 width=4) (4 rows) Which looks similar to oracle's plan: QPLAN --------------- SORT UNIQUE TABLE ACCESS FULL EGAL Our problem is that things getting worse when the table size increases, as described in my first mails. (Especially when the temp-file is split into parts) Reorganizing/normalizing the data is no solultion, because our application is just for analyzing the data. I tried out the trigger-idea: inserting a value into a table with an unique key only if still does't exists, it works, but .. select distinct is a much faster solution. An other way I tried is an PL/TCL function which stores all different values into a assoziative array in memory, by far the fastest solution - but returning the different values makes problems to me (I shouldn't say, but I put it into a file and "copy" it back). And it's not very nice - not as nice as "select distinct" :) Kind regards, Michael Contzen ------=_NextPart_000_0029_01C26955.76B204F0 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IjIMAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcA GAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEG gAMADgAAANIHCgABAA4AEgAAAAIABgEBA5AGAGAMAAAiAAAACwACAAEAAAAL ACMAAAAAAAMAJgAAAAAACwApAAAAAAADADYAAAAAAB4AcAABAAAAGwAAAEhv dyBzbG93IGlzIGRpc3RpbmN0IC0gMm5kAAACAXEAAQAAABYAAAABwmlEswfQ I5NW1E0R1rriAAECsAhSAAACAR0MAQAAAB8AAABTTVRQOk1JQ0hBRUwgQ09O VFpFTkBET0hMRS5DT00AAAsAAQ4AAAAAQAAGDgAMGpVEacIBAgEKDgEAAAAY AAAAAAAAAPy/fA9oDdURurwAAQKwCFLCgAAACwAfDgEAAAACAQkQAQAAAGsI AABnCAAASQ8AAExaRnVomg4lAwAKAHJjcGcxMjUWMgD4C2BuDhAwMzFPAfcC pARkAgBjaArAc/BldDAgCFUHsgKDAFCvA9QQ2QcTAoB9CoF2CJCkd2sLgGQ0 DGBjAFBDCwMLtSBIZWwJACwHCqIKhAqASSBwb3MSdAmAIHMDcGUgb4JiETBy dmF0aQIgqQQgdG8Y0GgX8HAEkNUCEHIDgWMX8WYXQwnBawQgF8N3CeBrEvAW g2HoZ28uFnRUGSIDYAJgtGVtGxBpGRAZBG8Fsa0ZXSIRMBzwYwVAZAQAcxiA GdB0IhewGIAWMCBkZXgfkXMsCuMKgGJ2dQVAFzB0CIEXoBjhd/0FsGsXkQhg BUAXwwlwHLEeZBUAAaAc8CNBc3VsxSDAIAuAIGEgHPAEEX0FoG0LUA3gGHAX kRZ0d1xheRkBA5EkoW0mgGYeaREgBUAXUguAZ3Mu6yD1FnQnHx0nHKEBEBmR 4wQgAiAgT3IA0CPxJMA7BuAhcTMRYBiAB4JmYXcXcQXAJqZQGlUcBRZ0V/cW ISDgAiBsJoApHySQGrHVCQB3KDBBIFFvGREd8d8KwCRxGhEIYSTAcCV0GJF8 IHUAkA8gLUcg9RlVIDxxdR1AGwIxEi04IChPHTMJcAdALyFiaTNwYS0EYHUC MDHjZBhwYSl7KDwWdCgxAheAILE1qzdwLjNiMSTAFMAq5jlwLjAuMTtAPFEW dEgHCxEmYAlwOiA0ICoQIDE4MBFgTUh62CBYZQIgIOAzPfI+QGRHQiGQREUG ABoQdFk9gi1SC3AJkCkWdFNLMzBAYEwLgHV4IOAyFC40PHA4BgBNUC3/IoAE oBYgQNUWehYQCXAZAwsBkRzwOhZ6bWM9I/cDMADwIHBnB0AWdCtQRyFiVCPT InB1AmAN4C5dRoIiRsUIUApAbQOgfN1HUXkZUEchSYBNBHAGkOsIkTQGLUtF K0tGS7lL4L1GxWlHI0lxC4AXgGcR0R9JgEU/HxUFoDexKCop9ydAA2FGcztG xlBDK1BK7MNNFgHAMjc3NBTgOXTbO3ADYHdDG0+PKB+GJJCfUL9HEFH0SutG yDY3VD/fOUccck4lBCARAHYX8Bhg+wpBBCBiEUAbIQOgEWA7kvw5OSTARGED kSCAIcAA0O8FQDIEJgYJcGgIYBEwO4P/QuAKwC8hH4IFECFhF5ErAP8UwANw LyBDGxxyC2AngTUA+wSQJoAtVn1XgGTgAZAigJkEIDFtK9AEIHdoAxAfF/Er BELgCYAEIDBtN/cbZRmBGQNzN4AX8GSEO5L7ODIoMEcRQCfiGQNW51TB/wQg O5IHgCxwCHFq1BZ0LAL/JBcY4BZ6bZMFkGDQHv9NgUFXqCJ8cHNxAyA+8i8B AHYvQbAWMBZ6NtKZRyI0bQ5APHAwOBtl/0FRBcBHIWgQPGA+QGawFnT8c3kE IHU3D0AbZUPpFzD5YtBuJwVAN7AEgRdwO5L/awQBIERRGdIkoRlaXdYfBf8W dFboIAA7kh8GVo8gAGTg3yr1ZiQFoACAeUF0LyFoOv824BDwZHRFK29PcF9x ZXHhkwtQMzAgeIZRL3mGoQdyH3MsaBA2Ljk3Of90j3WRAdB173WRZqEWewOg 7xkSJ1QjwGEBKGUNV4AaR/8jdAeRJNAXgCVgZ0FksSdQ5xzwFnQaETg2BdA/ kHqC8ygQcfBfdCVgIOAkoWjzfwWRO6GNpB8dV4BtCJBhLT+Q8iSRBQBscQeR GOEyNvU+UUIoMCgZEDBxMHGHIHteIQtgck5iJqNElxewaT56kShGgmbSgfEw cTIznjKR4UMbIaFm8G5rGQTfDwFC4AXAEQAEIG4xQCcQ/wBwJoCDoQ3gLDJo wzBxZIT/F8AkkGuxbfcg9UXzIIALU3tPxlbnKFd/RwWlP6XoURBVRVJZFnRQ TEH+TqXvqO5Sbarfq++s/639fUbFVQMAZIErUI3xF3A9UQ4gOTIxPiAusGAu ezxwm9A4DjA7MHcQa8M9y1PEHSFkGRA9NEDVRyH8LT4rUD/wACCv37DjD0Dz PiA7MDY1sbqyb6jms9M8S2WCYCSQpJ2zk2Vx/wYAI8ADoCrBmuO0JXWSsPF8 MDSwkrahtgomBbKbKN+8hEMfLoGbQwkAbxtQmgH+bQMQCsEY4WdEL1Cdo0Ul DlGnrKn/WapTT1JUOa9gTkmm8cPGRzNBQoJMP9BBQ0NFUwXwAEZVTEwgRUdB +kwWek8yQRy2MHEmoQVA+50SKBAgTmBqpCJRYQFm4P9eIZl8lngWZZ5BAQAE 8l3QHxegJvoAwAMQKCEoRXP9GVBjBzEvIcvYlfgasSVx33xVTiEY8DGjo9BS PsCY0P8AcJogJ/GHQBmSJYDUImr0/zhBMGKecBexCkAkYBiRIOC/XdAjwEFR kTUyTTBxajMw7wVAaLIAcAdAedTrKDwhptciwkSTBRBnTmEtQKA24P+4wQCA BJBqsyTQXWNOEhjw/5AxI9MdMwBwiVWvhCKAJoD/LwMGkCAVYtAHkHixIIag oX8iUiDRIWKw8KKu0oWQEm2/FQAdYCxl1iIYgi3bQbqRHzFTJmIhpuTiLTFM L1T6Q8ggZjewH1Ay8ps0F3D/BbCQAuFieeV8VV1l3mUscP8XwNQgGHFdMQrA KwDg4ScB+x0ABbB51rEnMcFSjRIsct8ngeX2ZNAhCAlwdAhwAwD/atfrJV1W AMBmQhy1GMMX4cooFzBzYNFsZHihirX/JnDjBBcxIXGgod5lljN9g1kFoHB5 IACgoWIA0Gv/OHHnQM7BKgCeVF0wZLAWdP8DABnhZOCecp5C+iKeQR8P9CA6 vxtLFLEjQUaQCyDrzZYWdE2bUWEWIBFxAjC/mjDflRZ1EnEWgxQhAAGAAAsA AYAIIAYAAAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwADgAggBgAAAAAAwAAA AAAAAEYAAAAAEIUAAAAAAAADAAeACCAGAAAAAADAAAAAAAAARgAAAABShQAA fW4BAB4ACYAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADkuMAAL AA2ACCAGAAAAAADAAAAAAAAARgAAAACChQAAAQAAAAsAOoAIIAYAAAAAAMAA AAAAAABGAAAAAA6FAAAAAAAAAwA8gAggBgAAAAAAwAAAAAAAAEYAAAAAEYUA AAAAAAADAD2ACCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAAsAUoAIIAYA AAAAAMAAAAAAAABGAAAAAAaFAAAAAAAAAwBTgAggBgAAAAAAwAAAAAAAAEYA AAAAAYUAAAAAAAACAfgPAQAAABAAAAD8v3wPaA3VEbq8AAECsAhSAgH6DwEA AAAQAAAA/L98D2gN1RG6vAABArAIUgIB+w8BAAAAiwAAAAAAAAA4obsQBeUQ GqG7CAArKlbCAABQU1RQUlguRExMAAAAAAAAAABOSVRB+b+4AQCqADfZbgAA AEM6XFdJTk5UXFByb2ZpbGVzXG1jXExvY2FsIFNldHRpbmdzXEFud2VuZHVu Z3NkYXRlblxNaWNyb3NvZnRcT3V0bG9va1xvdXRsb29rLnBzdAAAAwD+DwUA AAADAA00/TcAAAIBfwABAAAAOQAAADxORkJCTEtMTVBNTE9IT0xQR0FGUElF TUZDQUFBLk1pY2hhZWwgQ29udHplbkBEb2hsZS5jb20+AAAAAAMABhByQxRx AwAHEL4JAAADABAQAAAAAAMAERAAAAAAHgAIEAEAAABlAAAASEVMTE8sSVBP U1RFRFNPTUVPQlNFUlZBVElPTlNUT1RIRVBFUkZPUk1BTkNFT0ZQT1NUR1JF U1NPTUVXRUVLU0FHT1RIRVBST0JMRU1XSVRIVEhFUE9PUlBFUkZPUk1BTkNF TwAAAAC6sg== ------=_NextPart_000_0029_01C26955.76B204F0--