explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FNtD

Settings
# exclusive inclusive rows x rows loops node
1. 11.709 11,416.737 ↓ 11.0 54,254 1

Append (cost=1,196.25..199,838.31 rows=4,918 width=92) (actual time=2.482..11,416.737 rows=54,254 loops=1)

2.          

CTE datetime_range

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

4.          

CTE cu

5. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on royalties_customer cu (cost=0.00..2.31 rows=1 width=38) (actual time=0.009..0.014 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 31
6.          

CTE splits

7. 0.018 1.335 ↓ 2.3 7 1

GroupAggregate (cost=14.53..14.60 rows=3 width=40) (actual time=1.330..1.335 rows=7 loops=1)

  • Group Key: s_1.contract_id, s_1.ledger_id
8. 0.021 1.317 ↓ 2.3 7 1

Sort (cost=14.53..14.54 rows=3 width=13) (actual time=1.316..1.317 rows=7 loops=1)

  • Sort Key: s_1.contract_id, s_1.ledger_id
  • Sort Method: quicksort Memory: 25kB
9. 0.709 1.296 ↓ 2.3 7 1

Bitmap Heap Scan on royalties_split s_1 (cost=4.31..14.51 rows=3 width=13) (actual time=0.648..1.296 rows=7 loops=1)

  • Recheck Cond: (payee_id = 658)
  • Heap Blocks: exact=3
10. 0.587 0.587 ↓ 2.3 7 1

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.30 rows=3 width=0) (actual time=0.587..0.587 rows=7 loops=1)

  • Index Cond: (payee_id = 658)
11.          

CTE items

12. 9.437 17.936 ↓ 7.0 362 1

Hash Join (cost=0.10..1,178.84 rows=52 width=48) (actual time=1.502..17.936 rows=362 loops=1)

  • Hash Cond: (i_2.contract_id = s_2.contract_id)
13. 7.148 7.148 ↑ 1.0 58,046 1

Seq Scan on royalties_calculated_item_contract i_2 (cost=0.00..957.25 rows=58,925 width=12) (actual time=0.014..7.148 rows=58,046 loops=1)

14. 0.006 1.351 ↓ 2.3 7 1

Hash (cost=0.06..0.06 rows=3 width=40) (actual time=1.351..1.351 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 1.345 1.345 ↓ 2.3 7 1

CTE Scan on splits s_2 (cost=0.00..0.06 rows=3 width=40) (actual time=1.338..1.345 rows=7 loops=1)

16. 41.739 10,741.547 ↓ 15.6 53,788 1

Nested Loop (cost=0.49..164,155.60 rows=3,453 width=92) (actual time=2.481..10,741.547 rows=53,788 loops=1)

17.          

Initplan (for Nested Loop)

18. 0.020 0.020 ↑ 1.0 1 1

CTE Scan on cu cu_1 (cost=0.00..0.02 rows=1 width=38) (actual time=0.015..0.020 rows=1 loops=1)

19. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_2 (cost=0.00..0.02 rows=1 width=38) (never executed)

20. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on datetime_range (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

21. 18.974 18.974 ↓ 7.0 362 1

CTE Scan on items i (cost=0.00..1.04 rows=52 width=48) (actual time=1.504..18.974 rows=362 loops=1)

22. 10,680.810 10,680.810 ↓ 2.3 149 362

Index Scan using royalties_transaction_release_id_10b77fbc on royalties_transaction t (cost=0.43..3,155.83 rows=66 width=43) (actual time=0.207..29.505 rows=149 loops=362)

  • Index Cond: (item_id = i.item_id)
  • Filter: ((contract_id IS NULL) AND (paid_timestamp < $6) AND ((transaction_type)::text = ANY ('{income,expense}'::text[])) AND (customer_id = 3))
  • Rows Removed by Filter: 0
23. 0.240 663.481 ↑ 3.1 466 1

Hash Left Join (cost=8,482.75..34,437.77 rows=1,465 width=92) (actual time=579.056..663.481 rows=466 loops=1)

  • Hash Cond: (t_1.contract_id = s.contract_id)
24.          

Initplan (for Hash Left Join)

25. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on cu cu_3 (cost=0.00..0.02 rows=1 width=38) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_4 (cost=0.00..0.02 rows=1 width=38) (never executed)

27. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on datetime_range datetime_range_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)

28. 0.648 663.228 ↑ 3.1 466 1

Hash Left Join (cost=8,482.59..34,424.79 rows=1,465 width=47) (actual time=579.020..663.228 rows=466 loops=1)

  • Hash Cond: (t_1.item_id = i_1.id)
29. 382.248 382.248 ↑ 3.1 466 1

Index Scan using royalties_transaction_contract_id_99579882 on royalties_transaction t_1 (cost=0.43..25,938.79 rows=1,465 width=47) (actual time=298.498..382.248 rows=466 loops=1)

  • Index Cond: (contract_id IS NOT NULL)
  • Filter: ((paid_timestamp < $9) AND ((transaction_type)::text = ANY ('{income,expense}'::text[])) AND (customer_id = 3))
  • Rows Removed by Filter: 13561
30. 18.848 280.332 ↑ 1.2 58,046 1

Hash (cost=7,593.18..7,593.18 rows=71,118 width=8) (actual time=280.332..280.332 rows=58,046 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3275kB
31. 261.484 261.484 ↑ 1.2 58,046 1

Seq Scan on royalties_item i_1 (cost=0.00..7,593.18 rows=71,118 width=8) (actual time=0.362..261.484 rows=58,046 loops=1)

32. 0.004 0.009 ↓ 2.3 7 1

Hash (cost=0.06..0.06 rows=3 width=40) (actual time=0.009..0.009 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.005 0.005 ↓ 2.3 7 1

CTE Scan on splits s (cost=0.00..0.06 rows=3 width=40) (actual time=0.003..0.005 rows=7 loops=1)

Planning time : 5.948 ms
Execution time : 11,425.755 ms