Tom,
Turning enable_hashjoin off made the query run as it had on v7.3. We
have worked around this by changing the index from a function call to a
direct index on a new column with the results of the function
maintained by a trigger. Would there be performance issues from
leaving enable_hashj
Roger Ging <[EMAIL PROTECTED]> writes:
> See results below.
Thanks for the report. It seems the issue is that the estimate for the
number of matching rows is way off (870 vs 8):
>-> Index Scan using idx_program_mri_id_no_program on program p
> (cost=0.00..3400.74 rows=870 width=40) (actua
Turning enable_hashjoin off made the query run as it had on v7.3. We
have worked around this by changing the index from a function call to a
direct index on a new column with the results of the function maintained
by a trigger. Would there be performance issues from leaving
enable_hashjoin o
Roger Ging <[EMAIL PROTECTED]> writes:
> Ran vacuum analyse on both program and logfile tables. Estimates are
> more in line with reality now,
And they are what now? You really can't expect to get useful help here
when you're being so miserly with the details ...
FWIW, I suspect you could forc
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 t
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':
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
