Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3E7AA9FBEA3 for ; Fri, 6 Apr 2007 15:04:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 11164-07 for ; Fri, 6 Apr 2007 15:03:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.238]) by postgresql.org (Postfix) with ESMTP id 4F07A9FBE1C for ; Fri, 6 Apr 2007 15:03:52 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id r28so570504nza for ; Fri, 06 Apr 2007 11:03:51 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=N0DG58+HJXwA/5NtIeIUwfUmiUXoWyLgm8Q0IO3xEZB4TqdVvjc8Qe0dc4SCtJwLxBQVRebo44+8NfpcotEuCL5uNgNgC0Qk63fm8al3UvA7hXl6E3RbHOqQLVDBSRZwgfwDN2icVmyzVx+DG9nU1q+D2HRnsnWHst3JPv4EkRg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=IXPIQ7Rbs6zN47KVruPlADa1+gn5f7EyU1JXjF8YU45HVqgct5xQO/gV7eIydvKQctP/fFKtl+HCKjIdy9sQlz2jOZ1P76xmKt/SAVllrU35TdH0LtgdNckqkIS0uncpUZnnMqC/gRE+HcX7MxYk9wpZ6WQtWPspUgTBahVzmeQ= Received: by 10.114.156.1 with SMTP id d1mr1296922wae.1175882630824; Fri, 06 Apr 2007 11:03:50 -0700 (PDT) Received: by 10.114.157.12 with HTTP; Fri, 6 Apr 2007 11:03:50 -0700 (PDT) Message-ID: Date: Fri, 6 Apr 2007 12:03:50 -0600 From: "Jonathan Ellis" To: "Tom Lane" Subject: Re: Premature view materialization in 8.2? Cc: pgsql-performance@postgresql.org In-Reply-To: <15399.1175882434@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <28349.1175838196@sss.pgh.pa.us> <261.1175840636@sss.pgh.pa.us> <15092.1175881227@sss.pgh.pa.us> <15399.1175882434@sss.pgh.pa.us> X-Google-Sender-Auth: 9c06fe1d5e240554 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/173 X-Sequence-Number: 24050 On 4/6/07, Tom Lane wrote: > "Jonathan Ellis" writes: > > I can do that... you don't think the fact I mentioned, that > > redefining the view to leave out the expensive function fixes the > > problem, is relevant? > > Hm, I'd not have thought that an expensive function would get evaluated > partway up the join tree, but maybe that's wrong. You never did show > us the actual view definition ... It was in my original post unless it got clipped: The problem seems to be that clan_members_v contains a call to an expensive function: create or replace view clan_members_v as select cm.clan_id, cm.user_id, cp.party_id, cm.date_accepted, p.name as party_name, p_tp_total(p.id)::int as tp_total from clan_members cm, clan_participants cp, parties p where cm.user_id = p.user_id and p.id = cp.party_id ; p_tp_total takes around 50ms per row. If I create clan_members_v without the function call, the original query's speed goes to the 150ms range on 8.2 as well.