explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vYRr : with some tuning

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.211 243,826.722 ↓ 49.0 49 1

Sort (cost=33.46..33.47 rows=1 width=1,404) (actual time=243,826.706..243,826.722 rows=49 loops=1)

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

CTE data

3. 1.091 15,439.625 ↓ 3,233.0 3,233 1

WindowAgg (cost=16.61..16.66 rows=1 width=105) (actual time=15,438.194..15,439.625 rows=3,233 loops=1)

4. 7.261 15,438.534 ↓ 3,233.0 3,233 1

Sort (cost=16.61..16.62 rows=1 width=102) (actual time=15,438.183..15,438.534 rows=3,233 loops=1)

  • Sort Key: ((((min(new_avia_prices_1.price)))::numeric * (((m_1.flight_duration + m_1.flight_duration_b))::numeric + (((m_1.changes_duration + m_1.changes_duration_b))::numeric * 1.5))))
  • Sort Method: quicksort Memory: 551kB
5. 951.954 15,431.273 ↓ 3,233.0 3,233 1

Nested Loop (cost=0.83..16.60 rows=1 width=102) (actual time=0.035..15,431.273 rows=3,233 loops=1)

  • Join Filter: ((m_1.pair_hash)::text = (new_avia_prices_1.pair_hash)::text)
  • Rows Removed by Join Filter: 10449056
6. 5.178 5.178 ↓ 3,233.0 3,233 1

Index Scan using avia_pair_hash_unq on new_avia_models m_1 (cost=0.41..8.25 rows=1 width=82) (actual time=0.008..5.178 rows=3,233 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:0'::text)
7. 5,606.022 14,474.141 ↓ 3,233.0 3,233 3,233

GroupAggregate (cost=0.42..8.31 rows=1 width=37) (actual time=0.011..4.477 rows=3,233 loops=3,233)

  • Group Key: new_avia_prices_1.pair_hash
8. 8,868.119 8,868.119 ↓ 8,600.0 8,600 3,233

Index Scan using new_avia_prices_unq on new_avia_prices new_avia_prices_1 (cost=0.42..8.30 rows=1 width=37) (actual time=0.009..2.743 rows=8,600 loops=3,233)

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

CTE ids

10. 4.148 15,449.178 ↓ 49.0 49 1

Nested Loop Semi Join (cost=0.04..0.09 rows=1 width=516) (actual time=15,441.402..15,449.178 rows=49 loops=1)

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 32126
11. 15,438.564 15,438.564 ↓ 3,233.0 3,233 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=15,438.195..15,438.564 rows=3,233 loops=1)

12. 0.000 6.466 ↓ 10.0 10 3,233

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

13. 3.246 6.466 ↓ 10.0 10 3,233

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

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
14. 0.834 3.220 ↓ 434.0 434 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=3.091..3.220 rows=434 loops=1)

  • Group Key: data_1.data_hash
15. 2.386 2.386 ↓ 3,233.0 3,233 1

CTE Scan on data data_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..2.386 rows=3,233 loops=1)

16. 0.458 243,826.511 ↓ 49.0 49 1

Nested Loop Semi Join (cost=0.83..16.70 rows=1 width=1,404) (actual time=15,932.654..243,826.511 rows=49 loops=1)

  • Join Filter: ((m.pair_hash)::text = (ids.pair_hash)::text)
  • Rows Removed by Join Filter: 1176
17. 51.900 243,825.955 ↓ 49.0 49 1

Nested Loop (cost=0.83..16.63 rows=1 width=1,428) (actual time=15,932.640..243,825.955 rows=49 loops=1)

  • Join Filter: ((m.pair_hash)::text = (new_avia_prices.pair_hash)::text)
  • Rows Removed by Join Filter: 158368
18. 5.855 5.855 ↓ 3,233.0 3,233 1

Index Scan using avia_pair_hash_unq on new_avia_models m (cost=0.41..8.25 rows=1 width=1,359) (actual time=0.039..5.855 rows=3,233 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:0'::text)
19. 5,062.878 243,768.200 ↓ 49.0 49 3,233

GroupAggregate (cost=0.42..8.36 rows=1 width=69) (actual time=8.881..75.400 rows=49 loops=3,233)

  • Group Key: new_avia_prices.pair_hash
20. 116,106.729 238,705.322 ↓ 241.0 241 3,233

Nested Loop Semi Join (cost=0.42..8.34 rows=1 width=102) (actual time=4.890..73.834 rows=241 loops=3,233)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 414658
21. 11,383.393 11,383.393 ↓ 8,600.0 8,600 3,233

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.42..8.30 rows=1 width=102) (actual time=0.012..3.521 rows=8,600 loops=3,233)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:0'::text)
22. 111,215.200 111,215.200 ↓ 48.0 48 27,803,800

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.001..0.004 rows=48 loops=27,803,800)

23. 0.098 0.098 ↓ 25.0 25 49

CTE Scan on ids (cost=0.00..0.02 rows=1 width=516) (actual time=0.000..0.002 rows=25 loops=49)