From: Roger Ging <rging@paccomsys.com>
To: pgsql-performance@postgresql.org
Subject: Re: Followup - expression (functional) index use in joins
Date: Wed, 26 Nov 2003 13:29:16 -0800
Message-ID: <3FC51B2C.2030700@paccomsys.com> (raw)
In-Reply-To: <200311261912.01286.dev@archonet.com>
References: <3FC4F360.2090609@paccomsys.com>
<200311261912.01286.dev@archonet.com>
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.
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rging@paccomsys.com
Subject: Re: Followup - expression (functional) index use in joins
In-Reply-To: <3FC51B2C.2030700@paccomsys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox