X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1AE09D1DB70 for ; Thu, 21 Aug 2003 10:36:08 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 13918-09 for ; Thu, 21 Aug 2003 10:36:01 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 3B4B2D1BB3A for ; Thu, 21 Aug 2003 10:35:57 -0300 (ADT) Received: from nebula.skynet.be (nebula.skynet.be [195.238.2.112]) by www.postgresql.com (Postfix) with ESMTP id 78331CF499B for ; Thu, 21 Aug 2003 10:35:56 -0300 (ADT) Received: from pleyel.kotnet.org (157.77-200-80.adsl.skynet.be [80.200.77.157]) by nebula.skynet.be (8.12.9/8.12.9/Skynet-OUT-2.21) with ESMTP id h7LDZWPF017160 for ; Thu, 21 Aug 2003 15:35:32 +0200 (envelope-from ) Received: from slicks by pleyel.kotnet.org with local (Exim 3.36 #1 (Debian)) id 19ppbG-0001QM-00 for ; Thu, 21 Aug 2003 15:35:38 +0200 Date: Thu, 21 Aug 2003 15:35:38 +0200 From: Mathieu De Zutter To: pgsql-performance@postgresql.org Subject: Sorting a query on a view ignores an index Message-ID: <20030821133538.GA973@pleyel.kotnet.org> Reply-To: Mathieu De Zutter Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/299 X-Sequence-Number: 3115 Hi, I'm having a performance problem in postgresql. I have a rather complex view (attached) which, on itself, executes very fast, as it should. Normally this view is unordered. When I order the view itself (see comments in attachment), the view executes with about the same speed since the field i'm sorting on has an index. However, i dont want the view to be presorted, but sort it in the queries that use the view. When I do that, the index I have on that field seems to be ignored. It stretches so far that, when I sort the view on A and sort the query on A too, the query will try to sort _again_ _without_ index and thus lose all performance. I've attached the query-plans for the different cases. preordered: means the view itself is sorted with/without sorting: tells whether the query is sorted Note: the "NOT NULL" in the queries doesn't affect performance With kind regards, Mathieu