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 7C963329E47 for ; Fri, 10 Sep 2004 05:17:17 +0100 (BST) 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 06599-06 for ; Fri, 10 Sep 2004 04:17:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 07A90329E46 for ; Fri, 10 Sep 2004 05:17:12 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8A4HBU5014021; Fri, 10 Sep 2004 00:17:12 -0400 (EDT) To: ogjunk-pgjedan@yahoo.com Cc: pgsql-performance@postgresql.org Subject: Re: Costly "Sort Key" on indexed timestamp column In-reply-to: <20040909225137.77822.qmail@web12701.mail.yahoo.com> References: <20040909225137.77822.qmail@web12701.mail.yahoo.com> Comments: In-reply-to message dated "Thu, 09 Sep 2004 15:51:37 -0700" Date: Fri, 10 Sep 2004 00:17:11 -0400 Message-ID: <14020.1094789831@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/59 X-Sequence-Number: 8187 writes: > I'm tuning my PostgreSQL DB (7.3.4) and have come across a query that > doesn't use an index I created specially for it, and consequently takes > circa 2 seconds to run. :( > ... > The output of EXPLAIN ANALYZE follows. Note how 99% of the total cost > comes from "Sort Key: userinfo1_.create_date". No, you are misreading the output. 99% of the cost comes from the join steps. I think the problem is that you have forced a not-very-appropriate join order by use of INNER JOIN syntax, and so the plan is creating intermediate join outputs that are larger than they need be. See http://www.postgresql.org/docs/7.3/static/explicit-joins.html 7.4 is a bit more forgiving about this; compare http://www.postgresql.org/docs/7.4/static/explicit-joins.html regards, tom lane