explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5vV9

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 194.004 ↓ 24.5 98 1

GroupAggregate (cost=12,153.66..12,153.74 rows=4 width=44) (actual time=193.932..194.004 rows=98 loops=1)

  • Group Key: c.customerid
2.          

CTE validcustomers

3. 4.473 173.249 ↓ 44.0 25,280 1

Group (cost=4,943.64..4,947.95 rows=574 width=16) (actual time=164.185..173.249 rows=25,280 loops=1)

  • Group Key: c_1.customerid, a.salesforceid
4. 13.127 168.776 ↓ 44.0 25,280 1

Sort (cost=4,943.64..4,945.08 rows=574 width=16) (actual time=164.183..168.776 rows=25,280 loops=1)

  • Sort Key: c_1.customerid
  • Sort Method: quicksort Memory: 1,954kB
5. 9.566 155.649 ↓ 44.0 25,280 1

Nested Loop (cost=61.07..4,917.34 rows=574 width=16) (actual time=8.850..155.649 rows=25,280 loops=1)

6. 2.608 44.663 ↓ 39.9 25,355 1

Nested Loop (cost=60.64..4,464.07 rows=636 width=16) (actual time=8.825..44.663 rows=25,355 loops=1)

7. 0.003 0.029 ↑ 1.0 1 1

Nested Loop (cost=0.56..16.62 rows=1 width=16) (actual time=0.025..0.029 rows=1 loops=1)

8. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using oisocv_userid_salesforceid_orgunitid_idx on orgunitidsfromuserspanofcontrol_view y (cost=0.28..8.30 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((userid = 9,046) AND (salesforceid = 268))
  • Heap Fetches: 1
9. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_users_userid on users u (cost=0.28..8.30 rows=1 width=0) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (userid = 9,046)
  • Filter: (isdeleted = 0)
10. 36.339 42.026 ↓ 22.7 25,355 1

Bitmap Heap Scan on alignments a (cost=60.07..4,436.29 rows=1,116 width=24) (actual time=8.799..42.026 rows=25,355 loops=1)

  • Recheck Cond: ((salesforceid = 268) AND (orgunitid = y.orgunitid))
  • Filter: (isdeleted = 0)
  • Heap Blocks: exact=17,397
11. 5.687 5.687 ↓ 22.6 25,355 1

Bitmap Index Scan on alignments_salesforceid_orgunitid_customerid_idx (cost=0.00..59.80 rows=1,123 width=0) (actual time=5.686..5.687 rows=25,355 loops=1)

  • Index Cond: ((salesforceid = 268) AND (orgunitid = y.orgunitid))
12. 101.420 101.420 ↑ 1.0 1 25,355

Index Scan using pk_customers_customerid on customers c_1 (cost=0.43..0.70 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=25,355)

  • Index Cond: (customerid = a.customerid)
  • Filter: (isdeleted = 0)
13. 0.098 193.944 ↓ 75.0 300 1

Sort (cost=7,205.71..7,205.72 rows=4 width=16) (actual time=193.921..193.944 rows=300 loops=1)

  • Sort Key: c.customerid
  • Sort Method: quicksort Memory: 39kB
14. 0.268 193.846 ↓ 75.0 300 1

Nested Loop Left Join (cost=57.89..7,205.67 rows=4 width=16) (actual time=183.023..193.846 rows=300 loops=1)

15. 0.166 192.978 ↓ 75.0 300 1

Nested Loop Left Join (cost=57.47..7,203.06 rows=4 width=16) (actual time=183.009..192.978 rows=300 loops=1)

16. 0.027 191.912 ↓ 75.0 300 1

Nested Loop (cost=57.05..7,201.05 rows=4 width=24) (actual time=182.994..191.912 rows=300 loops=1)

  • Join Filter: (validcustomers.customerid = c.customerid)
17. 0.540 190.985 ↓ 75.0 300 1

Nested Loop (cost=56.62..7,198.97 rows=4 width=32) (actual time=182.973..190.985 rows=300 loops=1)

18. 0.337 187.745 ↓ 64.3 900 1

Hash Join (cost=56.19..7,191.63 rows=14 width=40) (actual time=182.934..187.745 rows=900 loops=1)

  • Hash Cond: (ia.entityid = validcustomers.customerid)
19. 0.144 4.759 ↓ 2.8 1,200 1

Nested Loop (cost=37.53..7,141.71 rows=422 width=32) (actual time=0.274..4.759 rows=1,200 loops=1)

20. 0.039 0.039 ↑ 1.0 1 1

Seq Scan on entitytypes et (cost=0.00..1.10 rows=1 width=8) (actual time=0.037..0.039 rows=1 loops=1)

  • Filter: ((isdeleted = 0) AND (lower(type) = 'customer'::text))
  • Rows Removed by Filter: 4
21. 0.644 4.576 ↓ 2.0 1,200 1

Nested Loop (cost=37.53..7,134.75 rows=586 width=40) (actual time=0.231..4.576 rows=1,200 loops=1)

22. 0.794 0.932 ↓ 1.0 600 1

Bitmap Heap Scan on userinsightpreference uipreference (cost=36.97..2,099.54 rows=586 width=16) (actual time=0.214..0.932 rows=600 loops=1)

  • Recheck Cond: (userid = 9,046)
  • Filter: (status = ANY ('{0,2}'::bigint[]))
  • Heap Blocks: exact=572
23. 0.138 0.138 ↓ 1.0 606 1

Bitmap Index Scan on userinsightpreference_userid_insightid_idx (cost=0.00..36.82 rows=586 width=0) (actual time=0.138..0.138 rows=606 loops=1)

  • Index Cond: (userid = 9,046)
24. 3.000 3.000 ↓ 2.0 2 600

Index Scan using unique_insightid_entitytype_and_entityid on insightassociations ia (cost=0.56..8.58 rows=1 width=24) (actual time=0.005..0.005 rows=2 loops=600)

  • Index Cond: ((insightid = uipreference.insightid) AND (entitytypeid = et.entitytypeid))
  • Filter: (isdeleted = 0)
25. 3.473 182.649 ↓ 44.0 25,280 1

Hash (cost=11.48..11.48 rows=574 width=8) (actual time=182.649..182.649 rows=25,280 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,244kB
26. 179.176 179.176 ↓ 44.0 25,280 1

CTE Scan on validcustomers (cost=0.00..11.48 rows=574 width=8) (actual time=164.188..179.176 rows=25,280 loops=1)

27. 2.700 2.700 ↓ 0.0 0 900

Index Scan using pk_insights_insightid on insights (cost=0.43..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=900)

  • Index Cond: (insightid = ia.insightid)
  • Filter: ((isdeleted = 0) AND (insighttype = 1) AND (expirydate >= now()))
  • Rows Removed by Filter: 1
28. 0.900 0.900 ↑ 1.0 1 300

Index Scan using pk_customers_customerid on customers c (cost=0.43..0.51 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=300)

  • Index Cond: (customerid = ia.entityid)
  • Filter: (isdeleted = 0)
29. 0.900 0.900 ↑ 1.0 1 300

Index Scan using suggestioninsightmapping_insightid_idx on suggestioninsightmapping sim (cost=0.42..0.49 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=300)

  • Index Cond: (insightid = insights.insightid)
  • Filter: (isdeleted = 0)
30. 0.600 0.600 ↑ 1.0 1 300

Index Scan using pk_suggestion_suggestionid on suggestion s (cost=0.42..0.64 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=300)

  • Index Cond: (suggestionid = sim.suggestionid)
  • Filter: (isdeleted = 0)