X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E5B4F9FA5D2 for ; Thu, 15 Jun 2006 20:23:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63180-07 for ; Thu, 15 Jun 2006 20:23:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id F1BA39FA160 for ; Thu, 15 Jun 2006 20:23:40 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so483128ugf for ; Thu, 15 Jun 2006 16:23:39 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=rAsik9v0Q7vuKj+leHVgk60IWIlNSCHEXT5hl1byHu156IbMflMI4T74kw2Qea8xxpFIXneKvzJccdanr9IrIMEpLA9CIX5KO8zgXrPhXAzlA0xYN381UhZYmne4eCxkt86+nv0Th7tnGCbcruTDXCUtLy3ihxjOPHqAy0MppyI= Received: by 10.66.252.4 with SMTP id z4mr2199493ugh; Thu, 15 Jun 2006 16:23:39 -0700 (PDT) Received: by 10.66.216.11 with HTTP; Thu, 15 Jun 2006 16:23:39 -0700 (PDT) Message-ID: Date: Thu, 15 Jun 2006 19:23:39 -0400 From: "John Vincent" Reply-To: pgsql-performance@lusis.org To: "Jim C. Nasby" Subject: Re: Optimizer internals Cc: "PGSQL Performance" In-Reply-To: <20060615222344.GB93655@pervasive.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13255_9231142.1150413819314" References: <1150396425.31200.66.camel@archimedes> <1150398064.31200.76.camel@archimedes> <20060615222344.GB93655@pervasive.com> X-Google-Sender-Auth: e74d7193c8279045 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/333 X-Sequence-Number: 19690 ------=_Part_13255_9231142.1150413819314 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline > decibel=# create index test on i ( sum(i) ); > ERROR: cannot use aggregate function in index expression > decibel=# > > BTW, there have been a number of proposals to negate the effect of not > having visibility info in indexes. Unfortunately, none of them have come > to fruition yet, mostly because it's a very difficult problem to solve. > But it is something that the community would like to see happen. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > Yeah we got the same thing when we tried it. I thought about the whole thing on the way home and the downside is that we might have to ditch pgsql. As far as implementing it, it might make sense to translate READ UNCOMMITTED to that new functionality. If the default isolation level stays the current level, the people who need it can use it via WITH UR or somesuch. I know it's not that easy but it's an idea. I'm also thinking that the table inheritance we're going to be taking advantage of in 8.1 on the new server might make the sequence scan less of an issue. The only reason the sequence scan really blows is that we have a single table with 220M rows and growing. ------=_Part_13255_9231142.1150413819314 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline


decibel=# create index test on i ( sum(i) );
ERROR:  cannot use aggregate function in index expression
decibel=#

BTW, there have been a number of proposals to negate the effect of not
having visibility info in indexes. Unfortunately, none of them have come
to fruition yet, mostly because it's a very difficult problem to solve.
But it is something that the community would like to see happen.
--
Jim C. Nasby, Sr. Engineering Consultant       jnasby@pervasive.com
Pervasive Software       http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Yeah we got the same thing when we tried it.

I thought about the whole thing on the way home and the downside is that we might have to ditch pgsql.

As far as implementing it, it might make sense to translate READ UNCOMMITTED to that new functionality. If the default isolation level stays the current level, the people who need it can use it via WITH UR or somesuch.

I know it's not that easy but it's an idea. I'm also thinking that the table inheritance we're going to be taking advantage of in 8.1 on the new server might make the sequence scan less of an issue. The only reason the sequence scan really blows is that we have a single table with 220M rows and growing.
------=_Part_13255_9231142.1150413819314--