explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cGY3 : SLOW

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 1,937.461 ↓ 23.0 23 1

Sort (cost=34.08..34.09 rows=1 width=855) (actual time=1,937.437..1,937.461 rows=23 loops=1)

  • Sort Key: ((((min(new_avia_prices.price)))::numeric * ((m.flight_duration)::numeric + ((m.changes_duration)::numeric * 1.5))))
  • Sort Method: quicksort Memory: 84kB
2.          

CTE data

3. 0.149 192.005 ↓ 352.0 352 1

WindowAgg (cost=16.92..16.97 rows=1 width=105) (actual time=191.840..192.005 rows=352 loops=1)

4. 0.323 191.856 ↓ 352.0 352 1

Sort (cost=16.92..16.93 rows=1 width=102) (actual time=191.825..191.856 rows=352 loops=1)

  • Sort Key: ((((min(new_avia_prices_1.price)))::numeric * ((m_1.flight_duration)::numeric + ((m_1.changes_duration)::numeric * 1.5))))
  • Sort Method: quicksort Memory: 74kB
5. 12.404 191.533 ↓ 352.0 352 1

Nested Loop (cost=0.82..16.91 rows=1 width=102) (actual time=0.020..191.533 rows=352 loops=1)

  • Join Filter: ((m_1.pair_hash)::text = (new_avia_prices_1.pair_hash)::text)
  • Rows Removed by Join Filter: 123552
6. 0.313 0.313 ↓ 352.0 352 1

Index Scan using avia_pair_hash_unq on new_avia_models m_1 (cost=0.41..8.43 rows=1 width=74) (actual time=0.005..0.313 rows=352 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556409600000:0:3:5024:4312:1:0:0'::text)
7. 72.512 178.816 ↓ 352.0 352 352

GroupAggregate (cost=0.41..8.45 rows=1 width=37) (actual time=0.006..0.508 rows=352 loops=352)

  • Group Key: new_avia_prices_1.pair_hash
8. 106.304 106.304 ↓ 1,049.0 1,049 352

Index Scan using new_avia_prices_unq on new_avia_prices new_avia_prices_1 (cost=0.41..8.43 rows=1 width=37) (actual time=0.005..0.302 rows=1,049 loops=352)

  • Index Cond: ((request_hash)::text = 'avia:1556409600000:0:3:5024:4312:1:0:0'::text)
9.          

CTE ids

10. 0.497 193.082 ↓ 23.0 23 1

Nested Loop Semi Join (cost=0.04..0.09 rows=1 width=516) (actual time=192.281..193.082 rows=23 loops=1)

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 3393
11. 191.881 191.881 ↓ 352.0 352 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=191.842..191.881 rows=352 loops=1)

12. 0.000 0.704 ↓ 10.0 10 352

Limit (cost=0.04..0.05 rows=1 width=40) (actual time=0.001..0.002 rows=10 loops=352)

13. 0.314 0.704 ↓ 10.0 10 352

Sort (cost=0.04..0.05 rows=1 width=40) (actual time=0.001..0.002 rows=10 loops=352)

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
14. 0.114 0.390 ↓ 294.0 294 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.354..0.390 rows=294 loops=1)

  • Group Key: data_1.data_hash
15. 0.276 0.276 ↓ 352.0 352 1

CTE Scan on data data_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.000..0.276 rows=352 loops=1)

16. 0.154 1,937.348 ↓ 23.0 23 1

Nested Loop Semi Join (cost=0.82..17.01 rows=1 width=855) (actual time=332.146..1,937.348 rows=23 loops=1)

  • Join Filter: ((m.pair_hash)::text = (ids.pair_hash)::text)
  • Rows Removed by Join Filter: 253
17. 1.737 1,937.171 ↓ 23.0 23 1

Nested Loop (cost=0.82..16.95 rows=1 width=875) (actual time=332.134..1,937.171 rows=23 loops=1)

  • Join Filter: ((m.pair_hash)::text = (new_avia_prices.pair_hash)::text)
  • Rows Removed by Join Filter: 8073
18. 0.490 0.490 ↓ 352.0 352 1

Index Scan using avia_pair_hash_unq on new_avia_models m (cost=0.41..8.43 rows=1 width=806) (actual time=0.015..0.490 rows=352 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556409600000:0:3:5024:4312:1:0:0'::text)
19. 378.752 1,934.944 ↓ 23.0 23 352

GroupAggregate (cost=0.41..8.50 rows=1 width=69) (actual time=0.928..5.497 rows=23 loops=352)

  • Group Key: new_avia_prices.pair_hash
20. 702.240 1,556.192 ↓ 199.0 199 352

Nested Loop Semi Join (cost=0.41..8.47 rows=1 width=102) (actual time=0.771..4.421 rows=199 loops=352)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 21498
21. 115.456 115.456 ↓ 1,049.0 1,049 352

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.41..8.43 rows=1 width=102) (actual time=0.007..0.328 rows=1,049 loops=352)

  • Index Cond: ((request_hash)::text = 'avia:1556409600000:0:3:5024:4312:1:0:0'::text)
22. 738.496 738.496 ↓ 21.0 21 369,248

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.001..0.002 rows=21 loops=369,248)

23. 0.023 0.023 ↓ 12.0 12 23

CTE Scan on ids (cost=0.00..0.02 rows=1 width=516) (actual time=0.000..0.001 rows=12 loops=23)

Planning time : 0.634 ms
Execution time : 1,937.584 ms