explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LOTd

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,176.442 ↓ 0.0 0 1

Limit (cost=47,418.82..47,418.84 rows=7 width=35) (actual time=9,176.442..9,176.442 rows=0 loops=1)

2. 0.005 9,176.440 ↓ 0.0 0 1

Sort (cost=47,418.82..47,418.84 rows=7 width=35) (actual time=9,176.440..9,176.440 rows=0 loops=1)

  • Sort Key: customers.customername
  • Sort Method: quicksort Memory: 25kB
3. 1,084.137 9,176.435 ↓ 0.0 0 1

Nested Loop (cost=1.14..47,418.72 rows=7 width=35) (actual time=9,176.435..9,176.435 rows=0 loops=1)

4. 200.916 1,837.592 ↓ 26.3 2,084,902 1

Nested Loop (cost=0.71..9,216.20 rows=79,181 width=16) (actual time=6.245..1,837.592 rows=2,084,902 loops=1)

5. 5.351 10.133 ↓ 12.8 153 1

Nested Loop (cost=0.14..655.21 rows=12 width=8) (actual time=6.225..10.133 rows=153 loops=1)

  • Join Filter: (orgunits.orgunitlevelid = ol.orgunitlevelid)
  • Rows Removed by Join Filter: 24912
6. 1.141 4.782 ↓ 8.9 5,013 1

Nested Loop (cost=0.00..604.51 rows=566 width=16) (actual time=2.018..4.782 rows=5,013 loops=1)

  • Join Filter: ((orgunits.treeleft >= orgunits_1.treeleft) AND (orgunits.treeright <= orgunits_1.treeright))
  • Rows Removed by Join Filter: 82
7. 0.610 2.366 ↑ 1.0 1 1

Nested Loop (cost=0.00..343.14 rows=1 width=8) (actual time=2.007..2.366 rows=1 loops=1)

  • Join Filter: (orgunits_1.orgunitid = uom.orgunitid)
  • Rows Removed by Join Filter: 5094
8. 0.348 0.348 ↑ 1.0 1 1

Seq Scan on userorgunitmapping uom (cost=0.00..94.50 rows=1 width=8) (actual time=0.006..0.348 rows=1 loops=1)

  • Filter: (userid = 5910)
  • Rows Removed by Filter: 4999
9. 1.408 1.408 ↑ 1.0 5,095 1

Seq Scan on orgunits orgunits_1 (cost=0.00..184.95 rows=5,095 width=16) (actual time=0.003..1.408 rows=5,095 loops=1)

10. 1.275 1.275 ↑ 1.0 5,095 1

Seq Scan on orgunits (cost=0.00..184.95 rows=5,095 width=24) (actual time=0.004..1.275 rows=5,095 loops=1)

11. 0.000 0.000 ↑ 1.0 5 5,013

Materialize (cost=0.14..8.26 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=5,013)

12. 0.010 0.010 ↑ 1.0 5 1

Index Scan using orgunitlevel_salesforceid_idx on orgunitlevel ol (cost=0.14..8.23 rows=5 width=8) (actual time=0.008..0.010 rows=5 loops=1)

  • Index Cond: (salesforceid = 313)
13. 1,626.543 1,626.543 ↓ 1.0 13,627 153

Index Scan using alignments_orgunitid_idx on alignments (cost=0.56..580.22 rows=13,320 width=24) (actual time=0.007..10.631 rows=13,627 loops=153)

  • Index Cond: (orgunitid = orgunits.orgunitid)
14. 6,254.706 6,254.706 ↓ 0.0 0 2,084,902

Index Scan using pk_customers_customerid on customers (cost=0.43..0.47 rows=1 width=27) (actual time=0.003..0.003 rows=0 loops=2,084,902)

  • Index Cond: (customerid = alignments.customerid)
  • Filter: ((customertypeid = 1) AND (lower(customername) ~~ '%PHARMACY%'::text))
  • Rows Removed by Filter: 1
Planning time : 1.247 ms