explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UIsb

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 86,112.838 ↓ 13.0 13 1

Sort (cost=33.72..33.72 rows=1 width=1,435) (actual time=86,112.834..86,112.838 rows=13 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: 53kB
2.          

CTE data

3. 1.141 16,178.421 ↓ 3,279.0 3,279 1

WindowAgg (cost=16.74..16.79 rows=1 width=105) (actual time=16,177.002..16,178.421 rows=3,279 loops=1)

4. 7.777 16,177.280 ↓ 3,279.0 3,279 1

Sort (cost=16.74..16.74 rows=1 width=102) (actual time=16,176.990..16,177.280 rows=3,279 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: 558kB
5. 1,008.465 16,169.503 ↓ 3,279.0 3,279 1

Nested Loop (cost=0.83..16.73 rows=1 width=102) (actual time=0.083..16,169.503 rows=3,279 loops=1)

  • Join Filter: ((m_1.pair_hash)::text = (new_avia_prices_1.pair_hash)::text)
  • Rows Removed by Join Filter: 10748562
6. 5.500 5.500 ↓ 3,279.0 3,279 1

Index Scan using avia_pair_hash_unq on new_avia_models m_1 (cost=0.41..8.43 rows=1 width=82) (actual time=0.019..5.500 rows=3,279 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
7. 5,830.062 15,155.538 ↓ 3,279.0 3,279 3,279

GroupAggregate (cost=0.42..8.26 rows=1 width=37) (actual time=0.018..4.622 rows=3,279 loops=3,279)

  • Group Key: new_avia_prices_1.pair_hash
8. 9,325.476 9,325.476 ↓ 8,461.0 8,461 3,279

Index Scan using new_avia_prices_unq on new_avia_prices new_avia_prices_1 (cost=0.42..8.24 rows=1 width=37) (actual time=0.016..2.844 rows=8,461 loops=3,279)

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

CTE ids

10. 4.140 16,188.037 ↓ 13.0 13 1

Nested Loop Semi Join (cost=0.04..0.09 rows=1 width=516) (actual time=16,180.234..16,188.037 rows=13 loops=1)

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 32713
11. 16,177.339 16,177.339 ↓ 3,279.0 3,279 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=16,177.003..16,177.339 rows=3,279 loops=1)

12. 0.000 6.558 ↓ 10.0 10 3,279

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

13. 3.341 6.558 ↓ 10.0 10 3,279

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

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
14. 0.826 3.217 ↓ 672.0 672 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=3.064..3.217 rows=672 loops=1)

  • Group Key: data_1.data_hash
15. 2.391 2.391 ↓ 3,279.0 3,279 1

CTE Scan on data data_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.000..2.391 rows=3,279 loops=1)

16. 0.094 86,112.781 ↓ 13.0 13 1

Nested Loop Semi Join (cost=0.83..16.83 rows=1 width=1,435) (actual time=21,308.123..86,112.781 rows=13 loops=1)

  • Join Filter: ((m.pair_hash)::text = (ids.pair_hash)::text)
  • Rows Removed by Join Filter: 78
17. 14.018 86,112.674 ↓ 13.0 13 1

Nested Loop (cost=0.83..16.76 rows=1 width=1,459) (actual time=21,308.111..86,112.674 rows=13 loops=1)

  • Join Filter: ((m.pair_hash)::text = (new_avia_prices.pair_hash)::text)
  • Rows Removed by Join Filter: 42614
18. 5.232 5.232 ↓ 3,279.0 3,279 1

Index Scan using avia_pair_hash_unq on new_avia_models m (cost=0.41..8.43 rows=1 width=1,390) (actual time=0.051..5.232 rows=3,279 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
19. 1,255.857 86,093.424 ↓ 13.0 13 3,279

GroupAggregate (cost=0.42..8.31 rows=1 width=69) (actual time=7.005..26.256 rows=13 loops=3,279)

  • Group Key: new_avia_prices.pair_hash
20. 47,725.845 84,837.567 ↓ 64.0 64 3,279

Nested Loop Semi Join (cost=0.42..8.28 rows=1 width=102) (actual time=6.504..25.873 rows=64 loops=3,279)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 109522
21. 9,368.103 9,368.103 ↓ 8,461.0 8,461 3,279

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.42..8.24 rows=1 width=102) (actual time=0.016..2.857 rows=8,461 loops=3,279)

  • Index Cond: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
22. 27,743.619 27,743.619 ↓ 13.0 13 27,743,619

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.001..0.001 rows=13 loops=27,743,619)

23. 0.013 0.013 ↓ 7.0 7 13

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