explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uh73

Settings
# exclusive inclusive rows x rows loops node
1. 312,896.237 340,754.430 ↑ 4.6 465,396 1

Nested Loop (cost=158,601.61..1,622,230.74 rows=2,148,265 width=152) (actual time=18,811.914..340,754.430 rows=465,396 loops=1)

2.          

CTE pair_history

3. 4.116 18,817.437 ↓ 6.5 966 1

Finalize GroupAggregate (cost=158,582.32..158,600.76 rows=149 width=24) (actual time=18,808.648..18,817.437 rows=966 loops=1)

  • Group Key: o_1.id, e.mean_id, e.pair_start
4. 50.216 18,813.321 ↓ 12.9 1,597 1

Gather Merge (cost=158,582.32..158,598.03 rows=124 width=24) (actual time=18,808.640..18,813.321 rows=1,597 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.766 18,763.105 ↓ 8.6 532 3 / 3

Partial GroupAggregate (cost=157,582.30..157,583.69 rows=62 width=24) (actual time=18,762.072..18,763.105 rows=532 loops=3)

  • Group Key: o_1.id, e.mean_id, e.pair_start
6. 1.599 18,762.339 ↓ 10.5 648 3 / 3

Sort (cost=157,582.30..157,582.45 rows=62 width=24) (actual time=18,762.061..18,762.339 rows=648 loops=3)

  • Sort Key: o_1.id, e.mean_id, e.pair_start
  • Sort Method: quicksort Memory: 55kB
  • Worker 0: Sort Method: quicksort Memory: 74kB
  • Worker 1: Sort Method: quicksort Memory: 66kB
7. 173.967 18,760.740 ↓ 10.5 648 3 / 3

Hash Join (cost=314.60..157,580.45 rows=62 width=24) (actual time=1,217.026..18,760.740 rows=648 loops=3)

  • Hash Cond: ((o_1.agency_id)::numeric = a.id)
8. 336.274 18,579.328 ↓ 4.9 162,892 3 / 3

Nested Loop (cost=0.98..157,174.95 rows=33,384 width=28) (actual time=1.650..18,579.328 rows=162,892 loops=3)

9. 12,867.607 12,867.607 ↓ 4.9 162,892 3 / 3

Parallel Index Scan using es_event_pairing_idx on es_event e (cost=0.56..100,573.46 rows=33,384 width=24) (actual time=1.288..12,867.607 rows=162,892 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. 5,375.447 5,375.447 ↑ 1.0 1 488,677 / 3

Index Scan using es_order_pk on es_order o_1 (cost=0.42..1.70 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=488,677)

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

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

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

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

  • Filter: (organization_id = '5660'::numeric)
  • Rows Removed by Filter: 5173
13. 4.015 18,877.291 ↓ 6.5 966 1

Nested Loop (cost=0.42..364.91 rows=149 width=976) (actual time=18,808.681..18,877.291 rows=966 loops=1)

14. 18,819.180 18,819.180 ↓ 6.5 966 1

CTE Scan on pair_history ph (cost=0.00..2.98 rows=149 width=24) (actual time=18,808.651..18,819.180 rows=966 loops=1)

15. 54.096 54.096 ↑ 1.0 1 966

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

  • Index Cond: (id = ph.id)
16. 170.016 8,980.902 ↑ 1.8 482 966

Append (cost=0.43..728.55 rows=875 width=28) (actual time=0.260..9.297 rows=482 loops=966)

17. 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))
18. 19.320 19.320 ↓ 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.020..0.020 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))
19. 16.422 16.422 ↓ 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.017..0.017 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. 1,007.538 1,007.538 ↑ 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.029..1.043 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))
21. 11.592 11.592 ↓ 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.55 rows=20 width=28) (actual time=0.012..0.012 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. 3,196.494 3,196.494 ↓ 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.062..3.309 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))
23. 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))
24. 16.422 16.422 ↓ 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.017..0.017 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))
25. 14.490 14.490 ↓ 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.015..0.015 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. 1,347.570 1,347.570 ↓ 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.055..1.395 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))
27. 3,157.854 3,157.854 ↓ 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.070..3.269 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))
Planning time : 14.959 ms
Execution time : 340,856.191 ms