explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DW0I

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 148.937 ↓ 30.0 30 1

Group (cost=4,996.16..4,996.16 rows=1 width=12) (actual time=148.929..148.937 rows=30 loops=1)

  • Group Key: c.customerid
2.          

CTE validcustomers

3. 5.515 134.798 ↓ 44.0 25,280 1

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

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

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

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

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

6. 2.346 32.695 ↓ 39.9 25,355 1

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

7. 0.001 0.021 ↑ 1.0 1 1

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

8. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

  • Index Cond: ((userid = 9,046) AND (salesforceid = 268))
  • Heap Fetches: 1
9. 0.009 0.009 ↑ 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.008..0.009 rows=1 loops=1)

  • Index Cond: (userid = 9,046)
  • Filter: (isdeleted = 0)
10. 24.918 30.328 ↓ 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.397..30.328 rows=25,355 loops=1)

  • Recheck Cond: ((salesforceid = 268) AND (orgunitid = y.orgunitid))
  • Filter: (isdeleted = 0)
  • Heap Blocks: exact=17,397
11. 5.410 5.410 ↓ 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.410..5.410 rows=25,355 loops=1)

  • Index Cond: ((salesforceid = 268) AND (orgunitid = y.orgunitid))
12. 76.065 76.065 ↑ 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.003..0.003 rows=1 loops=25,355)

  • Index Cond: (customerid = a.customerid)
  • Filter: (isdeleted = 0)
13. 0.015 148.929 ↓ 32.0 32 1

Sort (cost=48.21..48.21 rows=1 width=8) (actual time=148.927..148.929 rows=32 loops=1)

  • Sort Key: c.customerid
  • Sort Method: quicksort Memory: 26kB
14. 0.026 148.914 ↓ 32.0 32 1

Nested Loop (cost=13.23..48.20 rows=1 width=8) (actual time=130.260..148.914 rows=32 loops=1)

  • Join Filter: (cs.segmentvalueid = sv.segmentvalueid)
15. 0.000 148.856 ↓ 32.0 32 1

Nested Loop (cost=13.08..43.22 rows=1 width=24) (actual time=130.250..148.856 rows=32 loops=1)

16. 0.006 148.790 ↓ 34.0 34 1

Nested Loop (cost=8.89..30.53 rows=1 width=16) (actual time=130.241..148.790 rows=34 loops=1)

17. 3.334 148.682 ↓ 34.0 34 1

Hash Join (cost=8.46..22.11 rows=1 width=24) (actual time=130.229..148.682 rows=34 loops=1)

  • Hash Cond: (validcustomers.customerid = cs.customerid)
18. 141.075 141.075 ↓ 44.0 25,280 1

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

19. 1.406 4.273 ↓ 9,212.0 9,212 1

Hash (cost=8.45..8.45 rows=1 width=16) (actual time=4.273..4.273 rows=9,212 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 560kB
20. 2.867 2.867 ↓ 9,245.0 9,245 1

Index Scan using customersegment_productid_idx on customersegment cs (cost=0.43..8.45 rows=1 width=16) (actual time=0.031..2.867 rows=9,245 loops=1)

  • Index Cond: (productid IS NULL)
  • Filter: (isdeleted = 0)
21. 0.102 0.102 ↑ 1.0 1 34

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

  • Index Cond: (customerid = validcustomers.customerid)
  • Filter: (isdeleted = 0)
22. 0.034 0.068 ↑ 1.0 1 34

Bitmap Heap Scan on highvaluehcpconfiguration hvhc (cost=4.19..12.68 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=34)

  • Recheck Cond: (segmentvalueid = cs.segmentvalueid)
  • Filter: (isdeleted = 0)
  • Heap Blocks: exact=32
23. 0.034 0.034 ↑ 2.5 2 34

Bitmap Index Scan on highvaluehcpconfiguration_segmentvalueid_idx (cost=0.00..4.19 rows=5 width=0) (actual time=0.001..0.001 rows=2 loops=34)

  • Index Cond: (segmentvalueid = cs.segmentvalueid)
24. 0.032 0.032 ↑ 1.0 1 32

Index Scan using pk_segmentvalueid on segmentvalue sv (cost=0.15..4.97 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=32)

  • Index Cond: (segmentvalueid = hvhc.segmentvalueid)
  • Filter: (isdeleted = 0)