Thread: query taking too long

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

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> <P><FONT SIZE=3D2>How can I modify this to be more efficient?</FONT>

[Please don't post HTML...]

I think the first question to ask is whether it's even correct.  It
looks to me like the EXISTS doesn't depend on the outer query, so
it's effectively a constant, and assuming that the constant is TRUE,
*every* row of db2_pos_fac_rev_02 will be returned.

Also, have you done VACUUM ANALYZE lately?  The explain estimates
seem suspiciously low.

            regards, tom lane