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

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