explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BDdM

Settings
# exclusive inclusive rows x rows loops node
1. 1,701.237 4,819.220 ↑ 1.0 1 1

Limit (cost=20,000,335,695.01..20,000,335,695.02 rows=1 width=32) (actual time=4,819.190..4,819.220 rows=1 loops=1)

  • Functions: 281
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 27.002 ms, Inlining 28.927 ms, Optimization 1616.930 ms, Emission 1115.100 ms, Total 2787.959 ms
2.          

Initplan (for Limit)

3. 0.987 1,061.956 ↑ 1.0 1 1

Aggregate (cost=10,000,169,075.22..10,000,169,075.23 rows=1 width=32) (actual time=1,061.931..1,061.956 rows=1 loops=1)

4. 1,035.398 1,060.969 ↑ 1.0 7 1

Limit (cost=10,000,166,624.75..10,000,169,075.13 rows=7 width=32) (actual time=1,042.724..1,060.969 rows=7 loops=1)

  • -> Nested Loop Left Join (cost=10,000,166,624.75..10002049217.11 rows=5,378 width=32) (actual time=1,042.720..1060.960 rows=7 loops
  • -> Nested Loop Left Join (cost=10,000,166,624.20..10001725085.05 rows=5,378 width=929) (actual time=1,038.863..1052.788 rows=
  • -> Nested Loop Left Join (cost=10,000,166,623.65..10001677530.09 rows=5,378 width=913) (actual time=1,037.291..1049.737
  • -> Nested Loop Left Join (cost=10,000,166,621.43..10000755404.76 rows=5,378 width=881) (actual time=1,031.717..10
  • -> Nested Loop (cost=10,000,166,620.20..10000201484.21 rows=5,378 width=849) (actual time=1,027.601..1027.7
  • -> Sort (cost=10,000,166,619.77..10000166633.22 rows=5,378 width=345) (actual time=1,027.530..1027.55
  • Sort Key: "*SELECT* 1_1".begin_date DESC NULLS LAST
  • Sort Method: external merge Disk: 13,328kB
  • -> Result (cost=0.01..10000166286.53 rows=5,378 width=345) (actual time=0.077..799.944 rows
  • -> Append (cost=0.01..10000166232.75 rows=5,378 width=9) (actual time=0.075..758.654
  • -> Subquery Scan on "*SELECT* 1_1" (cost=0.01..0.03 rows=1 width=9) (actual ti
  • -> Unique (cost=0.01..0.02 rows=1 width=169) (actual time=0.006..0.007 r
  • -> Sort (cost=0.01..0.02 rows=0 width=169) (actual time=0.005..0.0
  • Sort Key: id
  • Sort Method: quicksort Memory: 25kB
  • -> Result (cost=0.00..0.00 rows=0 width=169) (actual time=0.
  • One-Time Filter: false
  • -> Subquery Scan on "*SELECT* 2_1" (cost=10,000,000,000.42..10000166205.83 rows=5
  • -> Index Scan using opportunity_pk on opportunity o_1 (cost=0.43..6.47 rows=1 width=848) (actual
  • Index Cond: (id = "*SELECT* 1_1".id)
  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner p_5 (cost=0.55..8.82 rows=1 width=32) (actual time
  • Index Cond: ((id_sugar)::text = (o_1.partner_id)::text)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,000,000,000.42..10,000,166,152.06 rows=5,377 width=169) (actual rows= loops=)

  • -> Index Only Scan using advisor_id_sugar on advisor adv_1 (cost=0
  • Index Cond: (id_sugar = '2040697876119550'::text)
  • Heap Fetches: 0
  • -> Seq Scan on opportunity o_2 (cost=10,000,000,000.00..10000166080.4
  • Filter: (((status).code)::text = ANY ('{219902325556334}'::tex
  • Rows Removed by Filter: 621,366
6. 9.079 9.079 ↑ 1.0 1 7

Subquery Scan on p_2 (cost=1.24..102.98 rows=1 width=32) (actual time=1.291..1.297 rows=1 loops=7)

  • -> Nested Loop Left Join (cost=1.24..102.72 rows=1 width=723) (actual time=0.742..0.747 rows=1 l
  • -> Nested Loop (cost=0.97..17.01 rows=1 width=451) (actual time=0.034..0.036 rows=1 loops=
  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner kp (cost=0.55..8.57
  • Index Cond: ((id_sugar)::text = (o_1.assigned_to)::text)
  • -> Index Scan using advisor_id_sugar on advisor adv_2 (cost=0.41..8.43 rows=1 width=
  • Index Cond: ((id_sugar)::text = (o_1.assigned_to)::text)
  • -> Subquery Scan on ov (cost=0.27..34.50 rows=1 width=32) (actual time=0.642..0.644 rows=1
  • -> Index Scan using oz_id_sugar on oz o_3 (cost=0.27..34.24 rows=1 width=126) (actua
  • Index Cond: ((id_sugar)::text = (adv_2.oz_id)::text)
  • -> Index Scan using address_pk on address a_2 (cost=0.42..8.69 rows=1 width=
  • Index Cond: (id = o_3.address_id)
  • -> Nested Loop (cost=0.97..17.27 rows=1 width=32) (actual time=0.483..0.486
  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner p (cos
  • Index Cond: ((id_sugar)::text = (o_3.head_id)::text)
  • -> Index Scan using advisor_id_sugar on advisor ad (cost=0.41..8.43 ro
  • Index Cond: ((id_sugar)::text = (o_3.head_id)::text)
  • -> Index Scan using address_pk on address a (cost=0.42..8.69 rows=1 width=32) (actual ti
  • Index Cond: (id = kp.primary_address)
  • -> Index Scan using address_pk on address a_1 (cost=0.42..8.69 rows=1 width=32) (never e
  • Index Cond: (id = kp.alt_address)
  • -> Index Scan using contact_pk on contact c (cost=0.43..8.45 rows=1 width=18) (actual ti
  • Index Cond: (id = kp.primary_email)
  • -> Index Scan using contact_pk on contact c_1 (cost=0.43..8.45 rows=1 width=18) (never e
  • Index Cond: (id = kp.secondary_email)
  • -> Index Scan using contact_pk on contact c_2 (cost=0.43..8.45 rows=1 width=18) (actual
  • Index Cond: (id = kp.phone_mobile)
  • -> Index Scan using contact_pk on contact c_3 (cost=0.43..8.45 rows=1 width=18) (never e
  • Index Cond: (id = kp.phone)
7. 12.663 12.887 ↑ 1.0 1 7

Subquery Scan on p_3 (cost=2.21..171.44 rows=1 width=32) (actual time=1.831..1.841 rows=1 loops=7)

  • -> Nested Loop Left Join (cost=2.21..171.18 rows=1 width=740) (actual time=1.340..1.349 rows=1 loops=7
  • -> Subquery Scan on p_4 (cost=1.24..102.98 rows=1 width=32) (actual time=1.241..1.247 rows=1 loo
  • -> Index Scan using address_pk on address a_1_1 (cost=0.42..8.69 rows=1 width=32) (actual time
  • Index Cond: (id = kp_1.primary_address)
  • -> Index Scan using address_pk on address a_1_2 (cost=0.42..8.69 rows=1 width=32) (actual time
  • Index Cond: (id = kp_1.alt_address)
  • -> Index Scan using contact_pk on contact c_4 (cost=0.43..8.45 rows=1 width=18) (actual time=0
  • Index Cond: (id = kp_1.primary_email)
  • -> Index Scan using contact_pk on contact c_5 (cost=0.43..8.45 rows=1 width=18) (never execute
  • Index Cond: (id = kp_1.secondary_email)
  • -> Index Scan using contact_pk on contact c_6 (cost=0.43..8.45 rows=1 width=18) (actual time=0
  • Index Cond: (id = kp_1.phone_mobile)
  • -> Index Scan using contact_pk on contact c_7 (cost=0.43..8.45 rows=1 width=18) (actual time=0
  • Index Cond: (id = kp_1.phone)
8. 0.224 0.224 ↑ 1.0 1 7

Nested Loop (cost=0.98..17.02 rows=1 width=540) (actual time=0.030..0.032 rows=1 loops=7)

  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_1 (cost=0.55..8.57 rows
  • Index Cond: ((id_sugar)::text = (o_1.contact_id)::text)
  • -> Index Scan using kapitol_contact_id_sugar on kapitol_contact cbase (cost=0.42..8.44 row
  • Index Cond: ((id_sugar)::text = (o_1.contact_id)::text)
  • -> Nested Loop Left Join (cost=1.24..102.72 rows=1 width=724) (actual time=0.710..0.715 ro
  • -> Nested Loop (cost=0.97..17.01 rows=1 width=420) (actual time=0.026..0.028 rows=1
  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_2 (cost=0.5
  • Index Cond: ((id_sugar)::text = (kp_1.assigned_to)::text)
  • -> Index Scan using advisor_id_sugar on advisor adv_3 (cost=0.41..8.43 rows=1
  • Index Cond: ((id_sugar)::text = (kp_1.assigned_to)::text)
  • -> Subquery Scan on ov_1 (cost=0.27..34.50 rows=1 width=32) (actual time=0.630..0.63
  • -> Index Scan using address_pk on address a_3 (cost=0.42..8.69 rows=1 width=32) (a
  • Index Cond: (id = kp_2.primary_address)
  • -> Index Scan using address_pk on address a_4 (cost=0.42..8.69 rows=1 width=32) (n
  • Index Cond: (id = kp_2.alt_address)
  • -> Index Scan using contact_pk on contact c_8 (cost=0.43..8.45 rows=1 width=18) (a
  • Index Cond: (id = kp_2.primary_email)
  • -> Index Scan using contact_pk on contact c_9 (cost=0.43..8.45 rows=1 width=18) (n
  • Index Cond: (id = kp_2.secondary_email)
  • -> Index Scan using contact_pk on contact c_10 (cost=0.43..8.45 rows=1 width=18) (
  • Index Cond: (id = kp_2.phone_mobile)
  • -> Index Scan using contact_pk on contact c_11 (cost=0.43..8.45 rows=1 width=18) (
  • Index Cond: (id = kp_2.phone)
9. 0.000 0.000 ↓ 0.0

Index Scan using oz_id_sugar on oz o_4 (cost=0.27..34.24 rows=1 width=126) (actual rows= loops=)

  • Index Cond: ((id_sugar)::text = (adv_3.oz_id)::text)
  • -> Index Scan using address_pk on address a_5 (cost=0.42..8.69 rows=1
  • Index Cond: (id = o_4.address_id)
  • -> Nested Loop (cost=0.97..17.27 rows=1 width=32) (actual time=0.491..
  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner p
  • Index Cond: ((id_sugar)::text = (o_4.head_id)::text)
  • -> Index Scan using advisor_id_sugar on advisor ad_1 (cost=0.41.
  • Index Cond: ((id_sugar)::text = (o_4.head_id)::text)
10. 3.605 3.605 ↑ 1.0 1 7

Subquery Scan on p_6 (cost=0.55..59.99 rows=1 width=32) (actual time=0.512..0.515 rows=1 loops=7)

  • -> Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_3 (cost=0.55..59.73 rows=1 width=532) (actual t
  • Index Cond: ((id_sugar)::text = (o_1.created_by)::text)
  • -> Index Scan using address_pk on address a_6 (cost=0.42..8.69 rows=1 width=32) (actual time=0.026..0.027
  • Index Cond: (id = kp_3.primary_address)
  • -> Index Scan using contact_pk on contact c_12 (cost=0.43..8.45 rows=1 width=18) (actual time=0.005..0.005
  • Index Cond: (id = kp_3.primary_email)
  • -> Index Scan using contact_pk on contact c_14 (cost=0.43..8.45 rows=1 width=18) (actual time=0.004..0.004
  • Index Cond: (id = kp_3.phone_mobile)
11.          

SubPlan (for Subquery Scan)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using address_pk on address a_7 (cost=0.42..8.69 rows=1 width=32) (never executed)

  • Index Cond: (id = kp_3.alt_address)
13. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_13 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_3.secondary_email)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_15 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_3.phone)
15. 1,062.382 2,056.027 ↑ 5,378.0 1 1

Subquery Scan on fs (cost=10,000,166,619.77..10,000,166,713.89 rows=5,378 width=32) (actual time=2,056.022..2,056.027 rows=1 loops=1)

16. 85.593 993.645 ↑ 5,378.0 1 1

Sort (cost=10,000,166,619.77..10,000,166,633.22 rows=5,378 width=345) (actual time=993.641..993.645 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".begin_date DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
17. 148.551 908.052 ↓ 84.4 453,963 1

WindowAgg (cost=0.01..10,000,166,286.53 rows=5,378 width=345) (actual time=846.201..908.052 rows=453,963 loops=1)

18. 759.490 759.501 ↓ 84.4 453,963 1

Append (cost=0.01..10,000,166,219.31 rows=5,378 width=4) (actual time=0.083..759.501 rows=453,963 loops=1)

  • -> Subquery Scan on "*SELECT* 2" (cost=10,000,000,000.42..10000166192.39 rows=5,377 width=4) (actual time=0.073..731.937
19. 0.002 0.011 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.01..0.03 rows=1 width=4) (actual time=0.009..0.011 rows=0 loops=1)

  • -> Nested Loop (cost=10,000,000,000.42..10000166138.62 rows=5,377 width=169) (actual time=0.071..699.672 rows=4,539
20. 0.001 0.009 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=169) (actual time=0.009..0.009 rows=0 loops=1)

  • -> Index Only Scan using advisor_id_sugar on advisor adv (cost=0.41..4.43 rows=1 width=0) (actual time=0
  • -> Seq Scan on opportunity o (cost=10,000,000,000.00..10000166080.42 rows=5,377 width=4) (actual time=0.039.
21. 0.003 0.008 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=169) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: id
  • Sort Method: quicksort Memory: 25kB
  • Index Cond: (id_sugar = '2040697876119550'::text)
  • Heap Fetches: 0
  • Filter: (((status).code)::text = ANY ('{219902325556334}'::text[]))
  • Rows Removed by Filter: 621,366
22. 0.005 0.005 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=169) (actual time=0.004..0.005 rows=0 loops=1)

  • One-Time Filter: false
Execution time : 4,850.006 ms