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 393C6D1BB02 for ; Fri, 22 Aug 2003 07:14:51 -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 80976-09 for ; Fri, 22 Aug 2003 07:14:21 -0300 (ADT) Received: from Tiger.server100.com (unknown [207.44.158.4]) by svr1.postgresql.org (Postfix) with ESMTP id 40E88D1BA8C for ; Fri, 22 Aug 2003 07:14:19 -0300 (ADT) Received: from [195.13.175.104] (helo=Xeon) by Tiger.server100.com with asmtp (Exim 4.20) id 19q8w0-0004JY-Qo for pgsql-performance@postgresql.org; Fri, 22 Aug 2003 05:14:21 -0500 Message-ID: <001201c36895$bc9a9910$1401010a@Xeon> Reply-To: From: To: Subject: Re: Sorting a query on a view ignores an index Date: Fri, 22 Aug 2003 13:11:19 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-4" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - Tiger.server100.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - piladzi-2.biz X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.3 tagged_above=0.0 required=5.0 tests=BAYES_30, NO_REAL_NAME, RCVD_IN_RFCI X-Spam-Level: * X-Archive-Number: 200308/311 X-Sequence-Number: 3127 Hello, Here is my exp. regarding VIEW (create VIEW view_name AS SELECT ....) optimization. I have found strange (or at least not very intelligent) behavior of query planner (v.7.3.4). Given task is to take data from 14 tables, join, group, sort, etc. I currently use about ten views referencing each other. I have done a lot of job to adjust 'postgresql.conf' configuration. Except common increase of shared_mem&buffer stuff I felt I was unable to do anything else. So, initialy, time to generate the Monthly report was about 10 sec. I started to play with GEQO.... Screaming success -- I downplayed query execution time about 3x JUST by setting geqo_threshold=3. Now my Monthly report takes about 3 sec. (Users are running that sequence of select queries (I call it invocation of the final view ;-) quite often for any given period of time -- usually from one week to half a year (rarely) so it is essential part of my dbvs. Maybe my exp. will encourage somebody to play with scary genetic stuff. ;-) Good luck & Best Regards, Alvis