Slow query

query.sql

The query with explain:
EXPLAIN ANALYZE               
SELECT id AS person_id FROM crm_person 
WHERE domain=131 AND 
(EXISTS(
  SELECT 1 FROM canalytics_content_person_junction 
  WHERE canalytics_content_person_junction.person_id=crm_person.id AND
  EXISTS(SELECT 1 FROM mediaanalytic_play_session ca WHERE ca.person_id=canalytics_content_person_junction.person_id AND 
  ca.content_name=canalytics_content_person_junction.content_name AND ca.content_id=canalytics_content_person_junction.content_id)));
                                                                                                 QUERY PLAN                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=42587.76..96295.06 rows=760 width=16) (actual time=1324.589..1495.960 rows=865 loops=1)
   Hash Cond: (crm_person.id = ca.person_id)
   ->  Bitmap Heap Scan on crm_person  (cost=1380.99..54636.91 rows=60189 width=16) (actual time=38.206..182.497 rows=60585 loops=1)
         Recheck Cond: (domain = 131)
         ->  Bitmap Index Scan on crm_person_domain_idx  (cost=0.00..1365.94 rows=60189 width=0) (actual time=21.033..21.033 rows=60585 loops=1)
               Index Cond: (domain = 131)
   ->  Hash  (cost=39685.27..39685.27 rows=121720 width=32) (actual time=1285.498..1285.498 rows=29337 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 1834kB
         ->  Nested Loop  (cost=10637.07..39685.27 rows=121720 width=32) (actual time=171.303..1262.611 rows=29337 loops=1)
               ->  HashAggregate  (cost=10636.63..10788.56 rows=15193 width=41) (actual time=171.250..196.007 rows=29344 loops=1)
                     ->  Seq Scan on mediaanalytic_play_session ca  (cost=0.00..9497.22 rows=151922 width=41) (actual time=0.004..38.487 rows=151922 loops=1)
               ->  Index Scan using canalytics_content_person_junction_person_id_idx on canalytics_content_person_junction  (cost=0.43..1.89 rows=1 width=40) (actual time=0.024..0.036 rows=1 loops=29344)
                     Index Cond: (person_id = ca.person_id)
                     Filter: ((ca.content_name = content_name) AND (ca.content_id = content_id))
                     Rows Removed by Filter: 8
 Total runtime: 1496.116 ms
 
 

  
  
  Tables:
  \d canalytics_content_person_junction
  Table "public.canalytics_content_person_junction"
    Column    |           Type           | Modifiers 
--------------+--------------------------+-----------
 domain       | integer                  | 
 issued       | timestamp with time zone | 
 content_name | text                     | 
 content_id   | uuid                     | 
 person_id    | uuid                     | 
Indexes:
    "canalytics_content_person_jun_domain_content_name_content_i_idx" UNIQUE, btree (domain, content_name, content_id, person_id)
    "canalytics_content_person_junction_person_id_idx" btree (person_id)
    
\d mediaanalytic_play_session
        Table "public.mediaanalytic_play_session"
      Column       |           Type           | Modifiers 
-------------------+--------------------------+-----------
 domain            | integer                  | 
 issued            | timestamp with time zone | 
 last_update       | timestamp with time zone | 
 session_id        | uuid                     | 
 mediatype_base    | text                     | 
 mediatype_format  | text                     | 
 source_identifier | text                     | 
 media_runningtime | numeric                  | 
 start_time        | numeric                  | 
 end_time          | numeric                  | 
 page_url          | text                     | 
 ip_address        | text                     | 
 location_latlon   | json                     | 
 identity_id       | uuid                     | 
 send_id           | uuid                     | 
 person_id         | uuid                     | 
 content_name      | text                     | 
 content_id        | uuid                     | 
Indexes:
    "new_mediaanalytic_play_session_session_id_key" UNIQUE CONSTRAINT, btree (session_id)
    "mediaanalytic_play_session_issued_idx1" btree (issued)
    "mediaanalytic_play_session_person_id_idx" btree (person_id)
    "mediaanalytic_play_session_send_id_idx1" btree (send_id)
    
    
\d crm_person
                    Table "public.crm_person"
          Column          |           Type           | Modifiers 
--------------------------+--------------------------+-----------
 domain                   | integer                  | 
 id                       | uuid                     | 
 created_time             | timestamp with time zone | 
 lastmodified_time        | timestamp with time zone | 
 fields$nickname          | text                     | 
 fields$firstname         | text                     | 
 fields$lastname          | text                     | 
 profile_photo            | text                     | 
 fields$province          | text                     | 
 fields$city              | text                     | 
 meta_hold                | boolean                  | 
 subscriber_subscribed    | boolean                  | 
 subscriber_interval      | integer                  | 
 flag_newlead             | boolean                  | 
 leadsource_type          | text                     | 
 leadsource_id            | uuid                     | 
 flag_purchased           | boolean                  | 
 subscriber_timezone      | text                     | 
 subscriber_location      | json                     | 
 subscriber_disallow_bulk | boolean                  | 
 virtualfields            | hstore                   | 
 backup_virtualfields     | hstore                   | 
 owner_uid                | integer                  | 
Indexes:
    "crm_person_uuid" UNIQUE, btree (id)
    "crm_person_domain_idx" btree (domain)
    "crm_person_name_dex" gin (((COALESCE("fields$firstname", ''::text) || ' '::text) || COALESCE("fields$lastname", ''::text)) gin_trgm_ops)