explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F3g7U

Settings
# exclusive inclusive rows x rows loops node
1. 13.424 16,749.433 ↑ 63.9 1,565 1

Nested Loop (cost=9.39..39,669,321.60 rows=100,000 width=180) (actual time=203.468..16,749.433 rows=1,565 loops=1)

2.          

CTE dl

3. 0.317 192.139 ↑ 1.0 1 1

Aggregate (cost=6.50..6.51 rows=1 width=32) (actual time=192.139..192.139 rows=1 loops=1)

4. 191.822 191.822 ↓ 3.1 1,565 1

Function Scan on consultant_fetch_downline (cost=0.25..5.25 rows=500 width=32) (actual time=191.690..191.822 rows=1,565 loops=1)

5. 2.362 248.734 ↓ 15.7 1,565 1

Nested Loop (cost=2.63..1,487.50 rows=100 width=21) (actual time=193.087..248.734 rows=1,565 loops=1)

6. 3.296 222.897 ↓ 15.7 1,565 1

Nested Loop (cost=2.19..814.30 rows=100 width=24) (actual time=193.067..222.897 rows=1,565 loops=1)

7. 2.009 194.561 ↓ 15.7 1,565 1

HashAggregate (cost=1.78..2.78 rows=100 width=32) (actual time=193.027..194.561 rows=1,565 loops=1)

  • Group Key: ("ANY_subquery".unnest)::text
8. 0.233 192.552 ↓ 15.7 1,565 1

Subquery Scan on ANY_subquery (cost=0.00..1.53 rows=100 width=32) (actual time=192.149..192.552 rows=1,565 loops=1)

9. 0.175 192.319 ↓ 15.7 1,565 1

ProjectSet (cost=0.00..0.53 rows=100 width=32) (actual time=192.149..192.319 rows=1,565 loops=1)

10. 192.144 192.144 ↑ 1.0 1 1

CTE Scan on dl (cost=0.00..0.02 rows=1 width=32) (actual time=192.144..192.144 rows=1 loops=1)

11. 25.040 25.040 ↑ 1.0 1 1,565

Index Scan using pk_consultant_consultant on consultant c (cost=0.42..8.12 rows=1 width=24) (actual time=0.016..0.016 rows=1 loops=1,565)

  • Index Cond: ((id)::text = ("ANY_subquery".unnest)::text)
12. 23.475 23.475 ↑ 1.0 1 1,565

Index Scan using pk_contact_person on person p (cost=0.43..6.73 rows=1 width=29) (actual time=0.015..0.015 rows=1 loops=1,565)

  • Index Cond: (id = c.person_id)
13. 187.800 187.800 ↑ 1,000.0 1 1,565

Function Scan on consultant_fetch_incentive_trip_info_v1 details (cost=0.25..10.25 rows=1,000 width=68) (actual time=0.120..0.120 rows=1 loops=1,565)

14.          

SubPlan (for Nested Loop)

15. 26.605 11,895.565 ↑ 1,000.0 1 1,565

Result (cost=0.00..267.76 rows=1,000 width=32) (actual time=7.600..7.601 rows=1 loops=1,565)

16. 11,868.960 11,868.960 ↑ 1,000.0 1 1,565

ProjectSet (cost=0.00..5.27 rows=1,000 width=32) (actual time=7.583..7.584 rows=1 loops=1,565)

17. 0.000 0.000 ↑ 1.0 1 1,565

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1,565)

18. 6.260 23.475 ↑ 1.0 1 1,565

Aggregate (cost=8.51..8.52 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1,565)

19. 17.215 17.215 ↑ 1.3 3 1,565

Index Only Scan using incentive_transaction_incentive_trip_id_consultant_id_idx on incentive_transaction (cost=0.42..8.50 rows=4 width=0) (actual time=0.010..0.011 rows=3 loops=1,565)

  • Index Cond: ((incentive_trip_id = '700ee46e-0162-411d-96be-7bfeceb0c83e'::uuid) AND (consultant_id = (c.id)::text))
  • Heap Fetches: 2,043
20. 4,262.187 4,380.435 ↑ 1.0 1 1,565

Aggregate (cost=120.36..120.37 rows=1 width=32) (actual time=2.799..2.799 rows=1 loops=1,565)

21. 18.709 46.950 ↑ 1.3 3 1,565

Hash Join (cost=14.86..19.58 rows=4 width=584) (actual time=0.024..0.030 rows=3 loops=1,565)

  • Hash Cond: (incent.id = it.incentive_id)
22. 7.896 7.896 ↑ 1.0 39 1,128

Seq Scan on incentive incent (cost=0.00..4.39 rows=39 width=59) (actual time=0.001..0.007 rows=39 loops=1,128)

23. 4.695 20.345 ↑ 1.3 3 1,565

Hash (cost=14.81..14.81 rows=4 width=557) (actual time=0.013..0.013 rows=3 loops=1,565)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
24. 4.685 15.650 ↑ 1.3 3 1,565

Hash Left Join (cost=1.46..14.81 rows=4 width=557) (actual time=0.008..0.010 rows=3 loops=1,565)

  • Hash Cond: (((it.additional ->> 'applicable_bonus_id'::text))::uuid = ib.id)
  • Join Filter: ((it.additional -> 'applicable_bonus_id'::text) IS NOT NULL)
25. 10.955 10.955 ↑ 1.3 3 1,565

Index Scan using incentive_transaction_incentive_trip_id_consultant_id_idx on incentive_transaction it (cost=0.42..13.75 rows=4 width=540) (actual time=0.005..0.007 rows=3 loops=1,565)

  • Index Cond: ((incentive_trip_id = '700ee46e-0162-411d-96be-7bfeceb0c83e'::uuid) AND ((consultant_id)::text = (c.id)::text))
26. 0.002 0.010 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=33) (actual time=0.010..0.010 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.008 0.008 ↑ 1.0 2 1

Seq Scan on incentive_bonus ib (cost=0.00..1.02 rows=2 width=33) (actual time=0.007..0.008 rows=2 loops=1)

28.          

SubPlan (for Aggregate)

29. 39.610 39.610 ↑ 1.0 1 3,961

Index Scan using pk_resources on resource (cost=0.29..8.31 rows=1 width=34) (actual time=0.010..0.010 rows=1 loops=3,961)

  • Index Cond: (((key)::text = (('EOS-REPORT.INCENTIVE-TRIP.INCENTIVE-TYPE.'::text || incent.incentive_type_id) || '.LABEL'::text)) AND ((locale_id)::text = 'EN'::text))
30. 31.688 31.688 ↑ 1.0 1 3,961

Index Scan using pk_resources on resource resource_1 (cost=0.29..8.31 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=3,961)

  • Index Cond: (((key)::text = (('EOS-REPORT.INCENTIVE-TRIP.INCENTIVE-NAME.'::text || incent.name) || '.LABEL'::text)) AND ((locale_id)::text = 'EN'::text))
31. 0.000 0.000 ↓ 0.0 0 3,961

Index Scan using pk_resources on resource resource_2 (cost=0.29..8.31 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=3,961)

  • Index Cond: (((key)::text = (('EOS-REPORT.INCENTIVE-TRIP.INCENTIVE-BONUS.'::text || ib.name) || '.LABEL'::text)) AND ((locale_id)::text = 'EN'::text))
Planning time : 1.218 ms
Execution time : 16,749.903 ms