explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hCGR

Settings
# exclusive inclusive rows x rows loops node
1. 220.566 362,491.915 ↑ 4.6 930,792 1

Append (cost=1,621,383.08..2,823,684.04 rows=4,293,932 width=133) (actual time=30,421.379..362,491.915 rows=930,792 loops=1)

2.          

CTE pair_history

3. 4.152 30,418.384 ↓ 6.5 966 1

Finalize GroupAggregate (cost=158,582.15..158,600.59 rows=149 width=24) (actual time=30,408.389..30,418.384 rows=966 loops=1)

  • Group Key: o.id, e.mean_id, e.pair_start
4. 49.700 30,414.232 ↓ 12.8 1,592 1

Gather Merge (cost=158,582.15..158,597.86 rows=124 width=24) (actual time=30,408.374..30,414.232 rows=1,592 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.924 30,364.532 ↓ 8.6 531 3 / 3

Partial GroupAggregate (cost=157,582.13..157,583.53 rows=62 width=24) (actual time=30,363.239..30,364.532 rows=531 loops=3)

  • Group Key: o.id, e.mean_id, e.pair_start
6. 1.081 30,363.608 ↓ 10.5 648 3 / 3

Sort (cost=157,582.13..157,582.29 rows=62 width=24) (actual time=30,363.228..30,363.608 rows=648 loops=3)

  • Sort Key: o.id, e.mean_id, e.pair_start
  • Sort Method: quicksort Memory: 70kB
  • Worker 0: Sort Method: quicksort Memory: 49kB
  • Worker 1: Sort Method: quicksort Memory: 76kB
7. 211.017 30,362.527 ↓ 10.5 648 3 / 3

Hash Join (cost=314.60..157,580.28 rows=62 width=24) (actual time=2,503.578..30,362.527 rows=648 loops=3)

  • Hash Cond: ((o.agency_id)::numeric = a.id)
8. 401.195 30,147.811 ↓ 4.9 162,746 3 / 3

Nested Loop (cost=0.98..157,174.78 rows=33,384 width=28) (actual time=2.310..30,147.811 rows=162,746 loops=3)

9. 26,817.188 26,817.188 ↓ 4.9 162,746 3 / 3

Parallel Index Scan using es_event_pairing_idx on es_event e (cost=0.56..100,572.46 rows=33,384 width=24) (actual time=1.821..26,817.188 rows=162,746 loops=3)

  • Index Cond: ((name = ANY ('{ORDER_IS_PAIR,ORDER_IS_UNPAIR}'::text[])) AND (pair_start >= '2019-12-01 00:00:00'::timestamp without time zone))
10. 2,929.428 2,929.428 ↑ 1.0 1 488,238 / 3

Index Scan using es_order_pk on es_order o (cost=0.42..1.70 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=488,238)

  • Index Cond: (id = e.order_id)
11. 0.014 3.699 ↑ 1.0 9 3 / 3

Hash (cost=313.50..313.50 rows=9 width=5) (actual time=3.699..3.699 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 3.685 3.685 ↑ 1.0 9 3 / 3

Seq Scan on agencies a (cost=0.00..313.50 rows=9 width=5) (actual time=2.816..3.685 rows=9 loops=3)

  • Filter: (organization_id = '5660'::numeric)
  • Rows Removed by Filter: 5173
13.          

CTE positions

14. 316,957.041 356,505.095 ↑ 4.6 465,396 1

Nested Loop (cost=0.85..1,462,782.49 rows=2,146,966 width=152) (actual time=30,421.363..356,505.095 rows=465,396 loops=1)

15. 4.297 30,460.892 ↓ 6.5 966 1

Nested Loop (cost=0.42..364.91 rows=149 width=976) (actual time=30,408.441..30,460.892 rows=966 loops=1)

16. 30,419.887 30,419.887 ↓ 6.5 966 1

CTE Scan on pair_history ph (cost=0.00..2.98 rows=149 width=24) (actual time=30,408.392..30,419.887 rows=966 loops=1)

17. 36.708 36.708 ↑ 1.0 1 966

Index Scan using es_order_pk on es_order o_1 (cost=0.42..2.43 rows=1 width=956) (actual time=0.038..0.038 rows=1 loops=966)

  • Index Cond: (id = ph.id)
18. 142.968 9,087.162 ↑ 1.8 482 966

Append (cost=0.43..728.36 rows=875 width=28) (actual time=0.274..9.407 rows=482 loops=966)

19. 20.286 20.286 ↓ 0.0 0 966

Index Scan using _hyper_1_248_chunk_position_reorder_chunk_idx on _hyper_1_248_chunk p (cost=0.43..88.21 rows=104 width=28) (actual time=0.021..0.021 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
20. 20.286 20.286 ↓ 0.0 0 966

Index Scan using _hyper_1_213_chunk_position_reorder_chunk_idx on _hyper_1_213_chunk p_1 (cost=0.43..95.09 rows=114 width=28) (actual time=0.021..0.021 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
21. 17.388 17.388 ↓ 0.0 0 966

Index Scan using _hyper_1_207_chunk_position_reorder_chunk_idx on _hyper_1_207_chunk p_2 (cost=0.43..95.00 rows=114 width=28) (actual time=0.018..0.018 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
22. 904.176 904.176 ↑ 3.4 28 966

Index Scan using _hyper_1_192_chunk_position_reorder_chunk_idx on _hyper_1_192_chunk p_3 (cost=0.43..74.83 rows=94 width=28) (actual time=0.030..0.936 rows=28 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
23. 10.626 10.626 ↓ 0.0 0 966

Index Scan using _hyper_1_270_chunk_position_reorder_chunk_idx on _hyper_1_270_chunk p_4 (cost=0.42..15.35 rows=20 width=28) (actual time=0.011..0.011 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
24. 3,350.088 3,350.088 ↓ 2.2 197 966

Index Scan using _hyper_1_182_chunk_position_reorder_chunk_idx on _hyper_1_182_chunk p_5 (cost=0.43..75.49 rows=89 width=28) (actual time=0.071..3.468 rows=197 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
25. 2.898 2.898 ↓ 0.0 0 966

Index Scan using _hyper_1_41_chunk_position_reorder_chunk_idx on _hyper_1_41_chunk p_6 (cost=0.12..0.17 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
26. 15.456 15.456 ↓ 0.0 0 966

Index Scan using _hyper_1_202_chunk_position_reorder_chunk_idx on _hyper_1_202_chunk p_7 (cost=0.43..89.28 rows=111 width=28) (actual time=0.016..0.016 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
27. 15.456 15.456 ↓ 0.0 0 966

Index Scan using _hyper_1_197_chunk_position_reorder_chunk_idx on _hyper_1_197_chunk p_8 (cost=0.43..63.21 rows=83 width=28) (actual time=0.016..0.016 rows=0 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
28. 1,482.810 1,482.810 ↓ 1.5 81 966

Index Scan using _hyper_1_178_chunk_position_reorder_chunk_idx on _hyper_1_178_chunk p_9 (cost=0.43..49.61 rows=53 width=28) (actual time=0.057..1.535 rows=81 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
29. 3,104.724 3,104.724 ↓ 1.9 176 966

Index Scan using _hyper_1_187_chunk_position_reorder_chunk_idx on _hyper_1_187_chunk p_10 (cost=0.43..77.74 rows=92 width=28) (actual time=0.071..3.214 rows=176 loops=966)

  • Index Cond: ((mean_id = ph.mean_id) AND (position_ts >= ph.pair_start) AND (position_ts <= ph.pair_end) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
30. 361,084.939 361,084.939 ↑ 4.6 465,396 1

CTE Scan on positions (cost=0.00..590,415.65 rows=2,146,966 width=133) (actual time=30,421.374..361,084.939 rows=465,396 loops=1)

31. 1,186.410 1,186.410 ↑ 4.6 465,396 1

CTE Scan on positions positions_1 (cost=0.00..590,415.65 rows=2,146,966 width=133) (actual time=0.026..1,186.410 rows=465,396 loops=1)

Planning time : 19.386 ms
Execution time : 362,733.675 ms