Howdy:
Running PostgreSQL 7.2.1 on RedHat 7.2.
I have a query performance question. I have a query
where I believe it should take a small amount of time
to return a value, but in fact, it has been going for
over a day.
[snip query and explain return value]
explain
SELECT *
-- INTO dev_pos_er_fac02
FROM
"db2_pos_fac_rev_02" -- <--note: this is a view joining two different tables
WHERE exists
(
select 1
from
"db2_pos_fac_rev_02"
where
("db2_pos_fac_rev_02"."pos_code" = 2
AND
"db2_pos_fac_rev_02"."type_serv_code" In ('A','S')
)
OR
(
"db2_pos_fac_rev_02"."pos_code" = 2
AND
"db2_pos_fac_rev_02"."line_code" In ('450','451','452','459')
)
OR
(
"db2_pos_fac_rev_02"."proc_1_code" In ('99281','99282','99283','99284','99285','99286','99287','99288')
)
)
;
--- explain ---
Query OK, 0 rows affected (0.38 sec)
NOTICE: QUERY PLAN:
Result (cost=22.50..299601.92 rows=1 width=817)
InitPlan
-> Merge Join (cost=204574.64..206116.34 rows=1 width=115)
-> Sort (cost=69.83..69.83 rows=1000 width=64)
-> Seq Scan on db2_pos_rev_02 b (cost=0.00..20.00 rows=1000 width=64)
-> Sort (cost=204504.81..204504.81 rows=615678 width=51)
-> Seq Scan on db2_pos_fac_02 a (cost=0.00..65621.78 rows=615678 width=51)
-> Hash Join (cost=22.50..299601.92 rows=1 width=817)
-> Seq Scan on db2_pos_fac_02 a (cost=0.00..65621.78 rows=615678 width=621)
-> Hash (cost=20.00..20.00 rows=1000 width=196)
-> Seq Scan on db2_pos_rev_02 b (cost=0.00..20.00 rows=1000 width=196)
[/snip]
I think someone was telling me that when you use IN, my queries tend
not to be as efficient as they could be. I don't know -
How can I modify this to be more efficient?
Thanks!
-X
