Skip site navigation (1) Skip section navigation (2)

Re: Followup - expression (functional) index use in joins

From: Roger Ging <rging(at)paccomsys(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Followup - expression (functional) index use in joins
Date: 2003-11-26 21:29:16
Message-ID: 3FC51B2C.2030700@paccomsys.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
>
>
>  
>

In response to

Responses

pgsql-performance by date

Next:From: Neil ConwayDate: 2003-11-26 22:03:31
Subject: Re: very large db performance question
Previous:From: LIANHE SHAODate: 2003-11-26 20:22:01
Subject: very large db performance question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group