X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 46D2FD1B445 for ; Wed, 26 Nov 2003 19:13:26 +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 93073-07 for ; Wed, 26 Nov 2003 15:13:01 -0400 (AST) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id B3BA9D1B48F for ; Wed, 26 Nov 2003 15:11:59 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 1AP553-000GEZ-0X; Wed, 26 Nov 2003 19:12:05 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 7E10B16570; Wed, 26 Nov 2003 19:12:04 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A2D3D15D31; Wed, 26 Nov 2003 19:12:02 +0000 (GMT) From: Richard Huxton To: Roger Ging , pgsql-performance@postgresql.org Subject: Re: Followup - expression (functional) index use in joins Date: Wed, 26 Nov 2003 19:12:01 +0000 User-Agent: KMail/1.5 References: <3FC4F360.2090609@paccomsys.com> In-Reply-To: <3FC4F360.2090609@paccomsys.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200311261912.01286.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/274 X-Sequence-Number: 4834 On Wednesday 26 November 2003 18:39, Roger Ging wrote: > version 7.4 results: > > explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN > music.program P ON > music.fn_mri_id_no_program(P.mri_id_no) = L.program_id > WHERE L.station = UPPER('kabc')::VARCHAR > AND L.air_date = '04/12/2002'::TIMESTAMP > AND P.cutoff_date IS NULL > ORDER BY L.chron_start,L.chron_end; > -> Seq Scan on program p (cost=0.00..15192.35 > rows=4335 width=20) (actual time=109.045..1955.882 rows=173998 loops=1) The estimated number of rows here (4335) is *way* off (173998 actually). If you only had 4335 rows, then this might be a more sensible plan. First step is to run: VACUUM ANALYSE program; Then, check the definition of your function fn_mri_id_no_program() and make sure it is marked immutable/stable (depending on what it does) and that it's returning a varchar. -- Richard Huxton Archonet Ltd