X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 121508B9E98 for ; Sun, 6 Feb 2005 17:17:15 +0000 (GMT) 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 34547-10 for ; Sun, 6 Feb 2005 17:17:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D2CEE8B9E40 for ; Sun, 6 Feb 2005 17:17:06 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j16HGsNe004860; Sun, 6 Feb 2005 12:16:55 -0500 (EST) To: lutzeb@aeccom.com Cc: Greg Stark , pgsql-performance@postgresql.org, john@arbash-meinel.com Subject: Re: query produces 1 GB temp file In-reply-to: <42061B35.6000306@aeccom.com> References: <20050205182117.0125074@mail2.aeccom.com> <87y8e2kb8j.fsf@stark.xeocode.com> <42061B35.6000306@aeccom.com> Comments: In-reply-to Dirk.Lutzebaeck@t-online.de (Dirk Lutzebaeck) message dated "Sun, 06 Feb 2005 14:27:17 +0100" Date: Sun, 06 Feb 2005 12:16:54 -0500 Message-ID: <4859.1107710214@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/93 X-Sequence-Number: 10422 Dirk.Lutzebaeck@t-online.de (Dirk Lutzebaeck) writes: > SELECT DISTINCT ON (df.val_9, df.created, df.flatid) df.docindex, > df.flatobj, bi.oid, bi.en > FROM bi,df > WHERE bi.rc=130170467 > ... > ORDER BY df.val_9 ASC, df.created DESC > LIMIT 1000 OFFSET 0 Just out of curiosity, what is this query supposed to *do* exactly? It looks to me like it will give indeterminate results. Practical uses of DISTINCT ON generally specify more ORDER BY columns than there are DISTINCT ON columns, because the extra columns determine which rows have priority to survive the DISTINCT filter. With the above query, you have absolutely no idea which row will be output for a given combination of val_9/created/flatid. regards, tom lane