Sequencial scan in a JOIN

[Andrew Jaimes <andrewjaimes@xxxxxxxxxxx>]

Hi everyone,

I am trying to run the following query:

SELECT  count(1) --DISTINCT l_userqueue.queueid
  FROM e_usersessions
  JOIN l_userqueue
    ON l_userqueue.userid = e_usersessions.entityid
  JOIN a_activity
    ON a_activity.activequeueid  = l_userqueue.queueid
   AND a_activity.vstatus        = 1
   AND a_activity.ventrydate     > 0
   AND a_activity.sbuid          = e_usersessions.sbuid
   AND a_activity.assignedtoid   = 0
   AND a_activity.status        <> '0'   
  WHERE e_usersessions.sessionkeepalivedatetime > 20120605082131943

Explain analyze:
'Aggregate  (cost=100402.10..100402.11 rows=1 width=0) (actual time=2249.051..2249.051 rows=1 loops=1)'
'  ->  Hash Join  (cost=10.93..99795.09 rows=242803 width=0) (actual time=0.541..2249.027 rows=33 loops=1)'
'        Hash Cond: ((a_activity.activequeueid = l_userqueue.queueid) AND (a_activity.sbuid = e_usersessions.sbuid))'
'        ->  Seq Scan on a_activity  (cost=0.00..88462.52 rows=1208167 width=22) (actual time=0.010..1662.142 rows=1207855 loops=1)'
'              Filter: ((ventrydate > 0) AND ((status)::text <> '0'::text) AND (vstatus = 1) AND (assignedtoid = 0::numeric))'
'        ->  Hash  (cost=10.86..10.86 rows=5 width=22) (actual time=0.053..0.053 rows=4 loops=1)'
'              ->  Hash Join  (cost=9.38..10.86 rows=5 width=22) (actual time=0.033..0.048 rows=4 loops=1)'
'                    Hash Cond: (l_userqueue.userid = e_usersessions.entityid)'
'                    ->  Seq Scan on l_userqueue  (cost=0.00..1.23 rows=23 width=27) (actual time=0.003..0.009 rows=23 loops=1)'
'                    ->  Hash  (cost=9.31..9.31 rows=5 width=21) (actual time=0.018..0.018 rows=2 loops=1)'
'                          ->  Index Scan using i06_e_usersessions on e_usersessions  (cost=0.00..9.31 rows=5 width=21) (actual time=0.009..0.012 rows=2 loops=1)'
'                                Index Cond: (sessionkeepalivedatetime > 20120605082131943::bigint)'
'Total runtime: 2249.146 ms'

I am trying to understand the reason why the a sequencial scan is used on a_activity instead of using the index by activequeueid (i08_a_activity). If I run the this other query, I get a complete different results:

SELECT *
 FROM a_activity
 WHERE a_activity.activequeueid  = 123456
   AND a_activity.vstatus        = 1
   AND a_activity.ventrydate     > 0

Explain analyze:
'Index Scan using i08_a_activity on a_activity  (cost=0.00..303.57 rows=162 width=7287) (actual time=0.019..0.019 rows=0 loops=1)'
'  Index Cond: ((activequeueid = 123456::numeric) AND (vstatus = 1) AND (ventrydate > 0))'
'Total runtime: 0.076 ms'


This is the definition of the index :

CREATE INDEX i08_a_activity
  ON a_activity
  USING btree
  (activequeueid , vstatus , ventrydate );


 a_activity table has 1,216,134 rows



Thanks in advance,
Andrew