Explain for query taking over 25mins to complete.

cspace.txt

SELECT
          h2.name                                                 AS "objectcsid",
          cc.objectnumber,
          h1.name                                                 AS "mediacsid",
          mc.description,
          bc.name,
          mc.creator                                                 creatorRefname,
          REGEXP_REPLACE(mc.creator, '^.*\)''(.*)''$', '\1')      AS "creator",
          mc.blobcsid,
          mc.copyrightstatement,
          mc.identificationnumber,
          mc.rightsholder                                            rightsholderRefname,
          REGEXP_REPLACE(mc.rightsholder, '^.*\)''(.*)''$', '\1') AS "rightsholder",
          mc.contributor,
          mp.approvedforweb,
          'notcard'                                               AS "imageType"
        FROM media_common mc
          JOIN media_pahma mp ON (mp.id = mc.id)
          LEFT OUTER JOIN hierarchy h1 ON (h1.id = mc.id)
          INNER JOIN relations_common rc ON (h1.name = rc.objectcsid AND rc.subjectdocumenttype = 'CollectionObject')
          LEFT OUTER JOIN hierarchy h2 ON (rc.subjectcsid = h2.name)
          LEFT OUTER JOIN collectionobjects_common cc ON (h2.id = cc.id)
          LEFT OUTER JOIN collectionobjects_pahma cp ON (h2.id = cp.id)
          JOIN hierarchy h3 ON (mc.blobcsid = h3.name)
          LEFT OUTER JOIN blobs_common bc ON (h3.id = bc.id)
        WHERE mc.id NOT IN (
          SELECT
            mc.id
          FROM media_common mc
          WHERE mc.description LIKE 'Primary catalog card%'
                OR mc.description ILIKE 'Catalog card%'
                OR mc.description ILIKE 'Bulk entry catalog card%'
                OR mc.description ILIKE 'Problematic catalog card%'
                OR mc.description ILIKE 'Recataloged objects catalog card%'
                OR mc.description ILIKE 'Revised catalog card%'
                OR mc.description ILIKE 'Index%'
                OR mc.description LIKE 'HSR datasheet%')
              AND mp.approvedforweb = 'true'
              AND h2.name NOT IN (
          SELECT
            h.name AS "objectcsid"
          FROM collectionobjects_common cc
            JOIN hierarchy h ON (cc.id = h.id)
            JOIN collectionobjects_pahma cp ON (cc.id = cp.id)
            FULL OUTER JOIN collectionobjects_pahma_pahmaobjectstatuslist osl0 ON (cc.id = osl0.id AND osl0.pos = 0)
            FULL OUTER JOIN collectionobjects_pahma_pahmaobjectstatuslist osl1 ON (cc.id = osl1.id AND osl1.pos = 1)
            FULL OUTER JOIN collectionobjects_pahma_pahmaobjectstatuslist osl2 ON (cc.id = osl2.id AND osl2.pos = 2)
          WHERE (cp.pahmatmslegacydepartment = 'Human Remains' AND osl0.item LIKE '%culturally%')
                OR (cp.pahmatmslegacydepartment = 'Human Remains' AND osl1.item LIKE '%culturally%')
                OR (cp.pahmatmslegacydepartment = 'Human Remains' AND osl2.item LIKE '%culturally%'));
  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=19913360.35..160798218.89 rows=4550937561 width=553)
   Hash Cond: ((h3.id)::text = (bc.id)::text)
   ->  Hash Join  (cost=19879359.47..91528621.75 rows=4550937561 width=568)
         Hash Cond: ((mp.id)::text = (mc.id)::text)
         ->  Merge Left Join  (cost=10519628.74..16504840.13 rows=171224087 width=143)
               Merge Cond: ((rc.subjectcsid)::text = (h2.name)::text)
               Filter: (NOT (hashed SubPlan 2))
               ->  Sort  (cost=3249550.33..3259596.60 rows=4018508 width=140)
                     Sort Key: rc.subjectcsid
                     ->  Merge Join  (cost=2520280.74..2602728.09 rows=4018508 width=140)
                           Merge Cond: ((h1.name)::text = (rc.objectcsid)::text)
                           ->  Sort  (cost=1227895.59..1228692.01 rows=318568 width=105)
                                 Sort Key: h1.name
                                 ->  Nested Loop  (cost=0.56..1198776.50 rows=318568 width=105)
                                       ->  Seq Scan on media_pahma mp  (cost=0.00..13746.54 rows=318568 width=38)
                                             Filter: approvedforweb
                                       ->  Index Scan using hierarchy_pk on hierarchy h1  (cost=0.56..3.71 rows=1 width=67)
                                             Index Cond: ((id)::text = (mp.id)::text)
                           ->  Materialize  (cost=1292302.67..1319608.47 rows=5461160 width=70)
                                 ->  Sort  (cost=1292302.67..1305955.57 rows=5461160 width=70)
                                       Sort Key: rc.objectcsid
                                       ->  Seq Scan on relations_common rc  (cost=0.00..521182.68 rows=5461160 width=70)
                                             Filter: ((subjectdocumenttype)::text = 'CollectionObject'::text)
               ->  Materialize  (cost=7166700.09..7358431.89 rows=38346360 width=38)
                     ->  Sort  (cost=7166700.09..7262565.99 rows=38346360 width=38)
                           Sort Key: h2.name
                           ->  Hash Left Join  (cost=43868.48..1587527.67 rows=38346360 width=38)
                                 Hash Cond: ((h2.id)::text = (cc.id)::text)
                                 ->  Seq Scan on hierarchy h2  (cost=0.00..1152689.60 rows=38346360 width=67)
                                 ->  Hash  (cost=34485.99..34485.99 rows=750599 width=45)
                                       ->  Seq Scan on collectionobjects_common cc  (cost=0.00..34485.99 rows=750599 width=45)
               SubPlan 2
                 ->  Nested Loop  (cost=2.26..103372.83 rows=2202 width=30)
                       ->  Nested Loop Left Join  (cost=1.70..97215.00 rows=2202 width=74)
                             Filter: (((osl0.item)::text ~~ '%culturally%'::text) OR ((osl1.item)::text ~~ '%culturally%'::text) OR ((osl2.item)::text ~~ '%culturally%'::text))
                             ->  Nested Loop Left Join  (cost=1.27..87920.68 rows=18634 width=98)
                                   ->  Nested Loop Left Join  (cost=0.85..78766.11 rows=18634 width=86)
                                         ->  Nested Loop  (cost=0.42..69611.54 rows=18634 width=74)
                                               ->  Seq Scan on collectionobjects_pahma cp  (cost=0.00..23091.08 rows=18634 width=37)
                                                     Filter: ((pahmatmslegacydepartment)::text = 'Human Remains'::text)
                                               ->  Index Only Scan using collectionobjects_common_pk on collectionobjects_common cc_1  (cost=0.42..2.49 rows=1 width=37)
                                                     Index Cond: (id = (cp.id)::text)
                                         ->  Index Scan using collectionobjects_pahma_pahmaobjectstatuslist_id_idx on collectionobjects_pahma_pahmaobjectstatuslist osl0  (cost=0.42..0.48 rows=1 width=49)
                                               Index Cond: ((cc_1.id)::text = (id)::text)
                                               Filter: (pos = 0)
                                   ->  Index Scan using collectionobjects_pahma_pahmaobjectstatuslist_id_idx on collectionobjects_pahma_pahmaobjectstatuslist osl1  (cost=0.42..0.48 rows=1 width=49)
                                         Index Cond: ((cc_1.id)::text = (id)::text)
                                         Filter: (pos = 1)
                             ->  Index Scan using collectionobjects_pahma_pahmaobjectstatuslist_id_idx on collectionobjects_pahma_pahmaobjectstatuslist osl2  (cost=0.42..0.48 rows=1 width=49)
                                   Index Cond: ((cc_1.id)::text = (id)::text)
                                   Filter: (pos = 2)
                       ->  Index Scan using hierarchy_pk on hierarchy h  (cost=0.56..2.79 rows=1 width=67)
                             Index Cond: ((id)::text = (cc_1.id)::text)
         ->  Hash  (cost=7750973.00..7750973.00 rows=19895738 width=536)
               ->  Merge Join  (cost=7287723.67..7750973.00 rows=19895738 width=536)
                     Merge Cond: ((mc.blobcsid)::text = (h3.name)::text)
                     ->  Sort  (cost=181102.64..182036.16 rows=373406 width=499)
                           Sort Key: mc.blobcsid
                           ->  Seq Scan on media_common mc  (cost=50220.03..86373.20 rows=373406 width=499)
                                 Filter: (NOT (hashed SubPlan 1))
                                 SubPlan 1
                                   ->  Seq Scan on media_common mc_1  (cost=0.00..49222.39 rows=399058 width=37)
                                         Filter: ((description ~~ 'Primary catalog card%'::text) OR (description~~* 'Catalog card%'::text) OR (description ~~* 'Bulk entry catalog card%'::text) OR (description ~~* 'Problematic catalog card%'::text) OR (description ~~* 'Recataloged objects catalog card%'::text) OR (description ~~* 'Revised catalog card%'::text) OR (description ~~* 'Index%'::text) OR (description ~~ 'HSR datasheet%'::text))
                     ->  Materialize  (cost=7106339.52..7298071.32 rows=38346360 width=67)
                           ->  Sort  (cost=7106339.52..7202205.42 rows=38346360 width=67)
                                 Sort Key: h3.name
                                 ->  Seq Scan on hierarchy h3  (cost=0.00..1152689.60 rows=38346360 width=67)
   ->  Hash  (cost=23768.17..23768.17 rows=818617 width=59)
         ->  Seq Scan on blobs_common bc  (cost=0.00..23768.17 rows=818617 width=59)
(69 rows)