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 388D0D1B43F for ; Wed, 26 Nov 2003 21:29:44 +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 78896-07 for ; Wed, 26 Nov 2003 17:29:19 -0400 (AST) Received: from pcs-1.paccomsys.com (208.225.nwc.net [207.151.225.208]) by svr1.postgresql.org (Postfix) with ESMTP id 6D2D8D1B482 for ; Wed, 26 Nov 2003 17:29:11 -0400 (AST) Received: from paccomsys.com (mail.musicreports.com [64.161.179.34]) by pcs-1.paccomsys.com (8.12.5/8.12.5) with ESMTP id hAQL5f74014340 for ; Wed, 26 Nov 2003 13:05:42 -0800 Message-ID: <3FC51B2C.2030700@paccomsys.com> Date: Wed, 26 Nov 2003 13:29:16 -0800 From: Roger Ging User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030708 Debian/1.3-4.lindows43 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Followup - expression (functional) index use in joins References: <3FC4F360.2090609@paccomsys.com> <200311261912.01286.dev@archonet.com> In-Reply-To: <200311261912.01286.dev@archonet.com> Content-Type: multipart/alternative; boundary="------------060905040807020201000309" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/278 X-Sequence-Number: 4838 This is a multi-part message in MIME format. --------------060905040807020201000309 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Ran vacuum analyse on both program and logfile tables. Estimates are more in line with reality now, but query still takes 10 seconds on v7.4 and 10 ms on v7.3. Function is marked as immutable and returns varchar(5). I am wondering why the planner would choose a merge join (v7.4) as opposed to a nested loop (v7.3) given the small number of rows in the top level table (logfile) based upon the where clause ( L.air_date = '04/12/2002'::TIMESTAMP ) there are typically only 30 rows per station/air_date. What am I missing here? Richard Huxton wrote: >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. > > > > --------------060905040807020201000309 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Ran vacuum analyse on both program and logfile tables.  Estimates are more in line with reality now, but query still takes 10 seconds on v7.4 and 10 ms on v7.3.  Function is marked as immutable and returns varchar(5).  I am wondering why the planner would choose a merge join (v7.4) as opposed to a nested loop (v7.3) given the small number of rows in the top level table (logfile) based upon the where clause (
L.air_date = '04/12/2002'::TIMESTAMP
)
there are typically only 30 rows per station/air_date.  What am I missing here?

Richard Huxton wrote:
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.


  
--------------060905040807020201000309--