Query execution time Vs Cost - Mailing list pgsql-performance

As part of one query tuning, it was observed that query execution time was more even though cost was decreased.

Initial Query :  Nested Loop Left Join  (cost=159.88..38530.02 rows=1 width=8) (actual time=0.387..40.766 rows=300 loops=1)

Changed Query :  Nested Loop Anti Join  (cost=171.66..5961.96 rows=1 width=8) (actual time=0.921..110.862 rows=300 loops=1)

May i know the reason behind in increase in response time, even though cost was reduced by 6.4 times.

Detailed execution plans can be found below along with the queries

Initial Query 

                                                                          QUERY PLAN                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=159.88..38530.02 rows=1 width=8) (actual time=0.387..40.766 rows=300 loops=1)
   Filter: ((ad.deleted IS NULL) OR (ad.deleted = 0))
   Buffers: shared hit=52138
   ->  Bitmap Heap Scan on konotor_user ku  (cost=159.73..38383.64 rows=712 width=8) (actual time=0.383..40.221 rows=300 loops=1)
         Recheck Cond: (((app_id = '12132818272260'::bigint) AND (user_type = 1)) OR ((app_id = '12132818272260'::bigint) AND (user_type = 2)))
         Filter: (NOT (SubPlan 1))
         Rows Removed by Filter: 485
         Heap Blocks: exact=729
         Buffers: shared hit=51838
         ->  BitmapOr  (cost=159.73..159.73 rows=1425 width=0) (actual time=0.112..0.112 rows=0 loops=1)
               Buffers: shared hit=11
               ->  Bitmap Index Scan on konotor_user_app_id_user_type_idx  (cost=0.00..88.42 rows=786 width=0) (actual time=0.009..0.009 rows=1 loops=1)
                     Index Cond: ((app_id = '12132818272260'::bigint) AND (user_type = 1))
                     Buffers: shared hit=4
               ->  Bitmap Index Scan on konotor_user_app_id_user_type_idx  (cost=0.00..70.95 rows=639 width=0) (actual time=0.101..0.101 rows=784 loops=1)
                     Index Cond: ((app_id = '12132818272260'::bigint) AND (user_type = 2))
                     Buffers: shared hit=7
         SubPlan 1
           ->  Nested Loop  (cost=0.57..45.28 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=785)
                 Buffers: shared hit=51098
                 ->  Index Scan using groups_app_id_group_id_idx on groups  (cost=0.28..20.33 rows=3 width=8) (actual time=0.002..0.014 rows=20 loops=785)
                       Index Cond: (app_id = ku.app_id)
                       Filter: (NOT deleted)
                       Rows Removed by Filter: 2
                       Buffers: shared hit=18888
                 ->  Index Only Scan using uk_groupid_userid on group_user gu  (cost=0.29..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=15832)
                       Index Cond: ((group_id = groups.group_id) AND (user_id = ku.user_id))
                       Heap Fetches: 455
                       Buffers: shared hit=32210
   ->  Index Scan using agent_details_user_id_idx on agent_details ad  (cost=0.15..0.19 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=300)
         Index Cond: (ku.user_id = user_id)
         Buffers: shared hit=300
 Planning time: 0.493 ms
 Execution time: 40.901 ms

=> explain(analyze,buffers,costs) SELECT ku.user_id FROM konotor_user ku LEFT OUTER JOIN agent_details ad ON ku.user_id = ad.user_id LEFT OUTER JOIN  (SELECT gu.user_id
                       FROM  group_user gu  INNER JOIN groups ON  gu.group_id = groups.group_id WHERE  app_id='12132818272260' AND groups.deleted = false)t ON t.user_id= ku.user_id
           WHERE     ku.app_id = '12132818272260'
           AND       (ku.user_type = 1 OR ku.user_type = 2)  AND (ad.deleted isnull OR ad.deleted = 0) AND t.user_id is NULL;

                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=171.66..5961.96 rows=1 width=8) (actual time=0.921..110.862 rows=300 loops=1)
   Buffers: shared hit=47730
   ->  Hash Left Join  (cost=171.10..5730.86 rows=1 width=8) (actual time=0.435..2.201 rows=785 loops=1)
         Hash Cond: (ku.user_id = ad.user_id)
         Filter: ((ad.deleted IS NULL) OR (ad.deleted = 0))
         Buffers: shared hit=743
         ->  Bitmap Heap Scan on konotor_user ku  (cost=160.09..5714.50 rows=1424 width=8) (actual time=0.208..1.327 rows=785 loops=1)
               Recheck Cond: (((app_id = '12132818272260'::bigint) AND (user_type = 1)) OR ((app_id = '12132818272260'::bigint) AND (user_type = 2)))
               Heap Blocks: exact=729
               Buffers: shared hit=740
               ->  BitmapOr  (cost=160.09..160.09 rows=1425 width=0) (actual time=0.116..0.116 rows=0 loops=1)
                     Buffers: shared hit=11
                     ->  Bitmap Index Scan on konotor_user_app_id_user_type_idx  (cost=0.00..88.42 rows=786 width=0) (actual time=0.010..0.010 rows=1 loops=1)
                           Index Cond: ((app_id = '12132818272260'::bigint) AND (user_type = 1))
                           Buffers: shared hit=4
                     ->  Bitmap Index Scan on konotor_user_app_id_user_type_idx  (cost=0.00..70.95 rows=639 width=0) (actual time=0.105..0.105 rows=784 loops=1)
                           Index Cond: ((app_id = '12132818272260'::bigint) AND (user_type = 2))
                           Buffers: shared hit=7
         ->  Hash  (cost=6.56..6.56 rows=356 width=10) (actual time=0.220..0.220 rows=356 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 23kB
               Buffers: shared hit=3
               ->  Seq Scan on agent_details ad  (cost=0.00..6.56 rows=356 width=10) (actual time=0.003..0.101 rows=356 loops=1)
                     Buffers: shared hit=3
   ->  Nested Loop  (cost=0.57..115.82 rows=1 width=8) (actual time=0.138..0.138 rows=1 loops=785)
         Buffers: shared hit=46987
         ->  Index Only Scan using uk_groupid_userid on group_user gu  (cost=0.29..115.12 rows=2 width=16) (actual time=0.135..0.135 rows=1 loops=785)
               Index Cond: (user_id = ku.user_id)
               Heap Fetches: 456
               Buffers: shared hit=45529
         ->  Index Scan using groups_pkey on groups  (cost=0.28..0.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=486)
               Index Cond: (group_id = gu.group_id)
               Filter: ((NOT deleted) AND (app_id = '12132818272260'::bigint))
               Rows Removed by Filter: 0
               Buffers: shared hit=1458
 Planning time: 0.534 ms
 Execution time: 110.999 ms
(36 rows)

Thanks in advance for your valuable time and inputs.

Regards, Amarendra