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 B4938D1B94A for ; Thu, 21 Aug 2003 17:12:36 -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 37738-01 for ; Thu, 21 Aug 2003 17:12:06 -0300 (ADT) Received: from viper.rx2.net (viper.rx2.net [66.78.14.162]) by svr1.postgresql.org (Postfix) with ESMTP id 315A4D1B8A3 for ; Thu, 21 Aug 2003 17:12:04 -0300 (ADT) Received: from cbbrowne by viper.rx2.net with local (Exim 4.20) id 19pvmT-0003kE-Dw; Thu, 21 Aug 2003 16:11:37 -0400 From: "Your Name" To: Mathieu De Zutter , Tom Lane , pgsql-performance@postgresql.org Subject: Re: Sorting a query on a view ignores an index X-Mailer: NeoMail 1.25 X-IPAddress: 207.136.25.10 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Message-Id: Date: Thu, 21 Aug 2003 16:11:37 -0400 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - viper.rx2.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [32025 32023] / [47 12] X-AntiAbuse: Sender Address Domain - viper.rx2.net X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/309 X-Sequence-Number: 3125 > Apart from avoiding views or subselects when sorting afterwards and > putting the whole bunch in a huge SQL statement (which i'll have to > produce on-the-fly), do you have an other alternative? > The 2 seconds is way to much, as the database will eventually run on a > machine that is 10 times slower. Something that isn't _totally_ clear is whether or not it is going to be possible to make use of indices in the selection. If the postmaster must assemble, out of disparate sources, a large collection of data, the best trade-off may very well be to build the collection the best way the system knows how (perhaps NOT ordering this using the index you expect), and sort it afterwards. Sorting doesn't tend to be grieviously expensive except when finding the query results is also grieviously expensive. I think you are assuming that the query would be quicker if it used the sorted index; that is an assumption that should be checked at the door, or at least checked somewhere. -- "The main difference between an amateur crypto designer and a used car salesman is that the used car salesman can probably drive and knows when he's lying." -- An Metet