X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 034EAD1D5A2 for ; Wed, 19 Nov 2003 06:29:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12862-07 for ; Wed, 19 Nov 2003 02:29:21 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 0F9E0D1C4EE for ; Wed, 19 Nov 2003 02:29:20 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 77704369E5; Wed, 19 Nov 2003 01:29:20 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AMLq4-00027g-00; Wed, 19 Nov 2003 01:29:20 -0500 To: "Randolf Richardson, DevNet SysOp 29" Cc: pgsql-sql@postgresql.org Subject: Re: Expressional Indexes References: <7141E2A2-04BE-11D8-81AE-0005029FC1A7@myrealbox.com> <200310221239.58302.josh@agliodbs.com> <18349.1066851898@sss.pgh.pa.us> <200310221322.26013.josh@agliodbs.com> In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 19 Nov 2003 01:29:20 -0500 Message-ID: <877k1w6fcv.fsf@stark.dyndns.tv> Lines: 23 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/232 X-Sequence-Number: 16047 "Randolf Richardson, DevNet SysOp 29" writes: > For example, if I want to index on a date field but only have the index > keep track of the most recent 30 days (and then create a secondary index for > all dates) so as to improve performance on more heavily loaded systems. > > Am I understanding this new terminology correctly? Thanks in advance. No, you could do the above using "partial indexes" but it wouldn't work very well in this case because the "last 30 days" keeps moving and you would have to keep redefining the index periodically. It also wouldn't really help performance. Expression Indexes are just more powerful "functional indexes". In 7.3 they could be used for indexing expressions like "lower(foo)". In 7.4 they're more powerful and you can index expressions other than simple function calls. They still should be things that always return the same value, which excludes subqueries. -- greg