Strange performance problem with query

Hi All,

Please see the output from the following query analysis :
=# explain analyze select count(1) from jbpmprocess.jbpm_taskinstance ti
join jbpmprocess.jbpm_task task on (ti.task_ = task.id_ ) join
jbpmprocess.jbpm_processinstance pi on ti.procinst_ = pi.id_ where
ti.isopen_ = true;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=47372.04..47372.05 rows=1 width=0) (actual
time=647.070..647.071 rows=1 loops=1)
    ->  Hash Join  (cost=44806.99..47336.72 rows=14127 width=0) (actual
time=605.077..645.410 rows=20359 loops=1)
          Hash Cond: (ti.task_ = task.id_)
          ->  Hash Join  (cost=44779.80..47115.28 rows=14127 width=8)
(actual time=604.874..640.541 rows=20359 loops=1)
                Hash Cond: (ti.procinst_ = pi.id_)
                ->  Index Scan using idx_task_instance_isopen on
jbpm_taskinstance ti  (cost=0.00..1995.84 rows=22672 width=16) (actual
time=0.011..16.606 rows=20359 loops=1)
                      Index Cond: (isopen_ = true)
                      Filter: isopen_
                ->  Hash  (cost=28274.91..28274.91 rows=1320391 width=8)
(actual time=604.601..604.601 rows=1320391 loops=1)
                      Buckets: 262144  Batches: 1  Memory Usage: 51578kB
                      ->  Seq Scan on jbpm_processinstance pi
(cost=0.00..28274.91 rows=1320391 width=8) (actual time=0.004..192.166
rows=1320391 loops=1)
          ->  Hash  (cost=18.75..18.75 rows=675 width=8) (actual
time=0.196..0.196 rows=675 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 27kB
                ->  Seq Scan on jbpm_task task  (cost=0.00..18.75
rows=675 width=8) (actual time=0.003..0.106 rows=675 loops=1)
  Total runtime: 652.266 ms
(15 rows)


I'm not sure why the planner insists on doing the sequential scan on
jbpm_processinstance even though the 22672 rows from jbpm_taskinstance
it has to match it against, is only 1% of the number of rows in
jbpm_processinstance. So far I think it is because the values in
procinst_ of jbpm_taskinstance are not entirely unique.

The very strange thing though is the way the query plan changes if I
repeat the where clause :

explain analyze select count(1) from jbpmprocess.jbpm_taskinstance ti
join jbpmprocess.jbpm_task task on (ti.task_ = task.id_ ) join
jbpmprocess.jbpm_processinstance pi on ti.procinst_ = pi.id_ where
ti.isopen_ = true and ti.isopen_ = true;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=2074.61..2074.62 rows=1 width=0) (actual
time=80.126..80.126 rows=1 loops=1)
    ->  Hash Join  (cost=27.19..2074.24 rows=151 width=0) (actual
time=0.217..77.959 rows=20359 loops=1)
          Hash Cond: (ti.task_ = task.id_)
          ->  Nested Loop  (cost=0.00..2044.97 rows=151 width=8) (actual
time=0.016..71.429 rows=20359 loops=1)
                ->  Index Scan using idx_task_instance_isopen on
jbpm_taskinstance ti  (cost=0.00..29.72 rows=243 width=16) (actual
time=0.012..16.928 rows=20359 loops=1)
                      Index Cond: ((isopen_ = true) AND (isopen_ = true))
                      Filter: (isopen_ AND isopen_)
                ->  Index Scan using jbpm_processinstance_pkey on
jbpm_processinstance pi  (cost=0.00..8.28 rows=1 width=8) (actual
time=0.002..0.002 rows=1 loops=20359)
                      Index Cond: (id_ = ti.procinst_)
          ->  Hash  (cost=18.75..18.75 rows=675 width=8) (actual
time=0.196..0.196 rows=675 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 27kB
                ->  Seq Scan on jbpm_task task  (cost=0.00..18.75
rows=675 width=8) (actual time=0.002..0.107 rows=675 loops=1)
  Total runtime: 80.170 ms

I get a similar plan selected on the original query if I set
enable_seqscan to off. I much prefer the second result.
My questions are:
1. Why is this happening?
2. How can I encourage the behavior of the second query without changing
the original query? Is there some column level setting I can set?

(BTW the tables are analyzed, and I currently have no special
settings/attributes set for any of the tables.)

--
Kind Regards
Stefan

Cell : 072-380-1479
Desk : 087-577-7241
To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser:
https://www.fnb.co.za/disclaimer.html

If you are unable to access the Disclaimer, send a blank e-mail to
firstrandbankdisclaimer@fnb.co.za and we will send you a copy of the Disclaimer.