Join (cost=39600.73..52775.08 rows=662853
width=8)"
"Merge Cond: ((vc1.short_desc_75)::text =
(vc2.short_desc_75)::text)"
"Join Filter: ((vc2.th_sku)::text <>
(vc1.th_sku)::text)"
"-> Sort (cost=19800.37..20062.75 rows=104954
width=27)"
" Sort Key: vc1.short_desc_75"
" -> Seq Scan on vendor_catalog vc1
(cost=0.00..8534.52 rows=104954 width=27)"
"Filter: (cutoff_date IS NULL)"
"-> Materialize (cost=19800.37..21112.29
rows=104954 width=27)"
" -> Sort (cost=19800.37..20062.75
rows=104954 width=27)"
"Sort Key: vc2.short_desc_75"
"-> Seq Scan on vendor_catalog vc2
(cost=0.00..8534.52 rows=104954 width=27)"
" Filter: (cutoff_date IS NULL)"
" -> Seq Scan on xc_products (cost=0.00..1716.99 rows=5132 width=8)"
"Filter: (xc_products.vc_th_sku IS NOT NULL)"
So, my question is, do I need to re-write all of my in() and not in ()
queries to left joins, is this something that might get resolved in
another release in the future?
Thanks for any help.
Roger Ging
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
The following query has never finished. I have let it run for over 24
hours. This is a one time update that is part of a conversion script
from MSSQL data. All of the tables are freshly built and inserted
into. I have not run explain analyze because it does not return in a
reasonable time.
true in
postgresql.conf and re-start server. PID shows up, so you can kill bad
queries from terminal and see CUP % in top
Roger Ging
V.P., Information Technology
Music Reports, Inc.
---(end of broadcast)---
TIP 9: the planner will ignor
e =
'2001-01-30 00:00:00'::timestamp without time zone))
-> Index Scan using idx_program_mri_id_no_program on program p
(cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8
loops=32)
Index Cond: (("outer".program_id)::text =
(music.fn_mr
can using idx_program_mri_id_no_program on program p
(cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8
loops=32)
Index Cond: (("outer".program_id)::text =
(music.fn_mri_id_no_program(p.mri_id_no))::text)
Total runtime: 5.637 ms
(6 rows)
Tom Lane wrote:
Roger Ging <[EMAIL
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'::TIMES
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
7.3.4 and takes over 10 seconds on version 7.4. Any
help will be appreciated.
Roger Ging
Query:
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 = &
