explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F427 : Optimization for: plan #lZrY

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.123 275.596 ↓ 57.0 57 1

Sort (cost=13,473.89..13,473.90 rows=1 width=1,379) (actual time=275.584..275.596 rows=57 loops=1)

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

CTE data

3. 1.145 49.603 ↓ 3,187.0 3,187 1

WindowAgg (cost=6,736.83..6,736.88 rows=1 width=105) (actual time=48.229..49.603 rows=3,187 loops=1)

4. 1.561 48.458 ↓ 3,187.0 3,187 1

Sort (cost=6,736.83..6,736.84 rows=1 width=102) (actual time=48.219..48.458 rows=3,187 loops=1)

  • Sort Key: (((p.min)::numeric * (((m.flight_duration + m.flight_duration_b))::numeric + (((m.changes_duration + m.changes_duration_b))::numeric * 1.5))))
  • Sort Method: quicksort Memory: 545kB
5. 4.931 46.897 ↓ 3,187.0 3,187 1

Hash Join (cost=6,732.78..6,736.82 rows=1 width=102) (actual time=40.780..46.897 rows=3,187 loops=1)

  • Hash Cond: ((m.pair_hash)::text = (p.pair_hash)::text)
6. 1.282 1.685 ↓ 3,187.0 3,187 1

Bitmap Heap Scan on new_avia_models m (cost=4.43..8.44 rows=1 width=82) (actual time=0.473..1.685 rows=3,187 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:2:1'::text)
  • Heap Blocks: exact=639
7. 0.403 0.403 ↓ 3,187.0 3,187 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..4.43 rows=1 width=0) (actual time=0.402..0.403 rows=3,187 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:2:1'::text)
8. 0.880 40.281 ↓ 3,187.0 3,187 1

Hash (cost=6,728.34..6,728.34 rows=1 width=37) (actual time=40.281..40.281 rows=3,187 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 247kB
9. 0.420 39.401 ↓ 3,187.0 3,187 1

Subquery Scan on p (cost=6,728.31..6,728.34 rows=1 width=37) (actual time=35.298..39.401 rows=3,187 loops=1)

10. 2.326 38.981 ↓ 3,187.0 3,187 1

GroupAggregate (cost=6,728.31..6,728.33 rows=1 width=37) (actual time=35.298..38.981 rows=3,187 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
11. 10.471 36.655 ↓ 8,751.0 8,751 1

Sort (cost=6,728.31..6,728.32 rows=1 width=37) (actual time=35.291..36.655 rows=8,751 loops=1)

  • Sort Key: new_avia_prices_1.pair_hash
  • Sort Method: quicksort Memory: 1068kB
12. 26.184 26.184 ↓ 8,751.0 8,751 1

Seq Scan on new_avia_prices new_avia_prices_1 (cost=0.00..6,728.30 rows=1 width=37) (actual time=23.937..26.184 rows=8,751 loops=1)

  • Filter: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:2:1'::text)
  • Rows Removed by Filter: 170849
13.          

CTE ids

14. 4.151 59.091 ↓ 57.0 57 1

Nested Loop Semi Join (cost=0.04..0.09 rows=1 width=516) (actual time=51.245..59.091 rows=57 loops=1)

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 31599
15. 48.566 48.566 ↓ 3,187.0 3,187 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=48.230..48.566 rows=3,187 loops=1)

16. 0.000 6.374 ↓ 10.0 10 3,187

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

17. 3.335 6.374 ↓ 10.0 10 3,187

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

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
18. 0.765 3.039 ↓ 455.0 455 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=2.889..3.039 rows=455 loops=1)

  • Group Key: data_1.data_hash
19. 2.274 2.274 ↓ 3,187.0 3,187 1

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

20. 0.539 275.473 ↓ 57.0 57 1

Nested Loop (cost=6,728.77..6,736.91 rows=1 width=1,379) (actual time=155.735..275.473 rows=57 loops=1)

  • Join Filter: ((m2.pair_hash)::text = (new_avia_prices.pair_hash)::text)
  • Rows Removed by Join Filter: 3192
21. 17.189 89.570 ↓ 57.0 57 1

Nested Loop Semi Join (cost=0.42..8.48 rows=1 width=1,850) (actual time=59.174..89.570 rows=57 loops=1)

  • Join Filter: ((m2.pair_hash)::text = (ids.pair_hash)::text)
  • Rows Removed by Join Filter: 180006
22. 2.267 2.267 ↓ 3,187.0 3,187 1

Index Scan using avia_pair_hash_unq on new_avia_models m2 (cost=0.42..8.44 rows=1 width=1,334) (actual time=0.020..2.267 rows=3,187 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:2:1'::text)
23. 70.114 70.114 ↓ 56.0 56 3,187

CTE Scan on ids (cost=0.00..0.02 rows=1 width=516) (actual time=0.016..0.022 rows=56 loops=3,187)

24. 87.552 185.364 ↓ 57.0 57 57

GroupAggregate (cost=6,728.35..6,728.38 rows=1 width=69) (actual time=1.722..3.252 rows=57 loops=57)

  • Group Key: new_avia_prices.pair_hash
25. 1.515 97.812 ↓ 297.0 297 57

Sort (cost=6,728.35..6,728.36 rows=1 width=102) (actual time=1.693..1.716 rows=297 loops=57)

  • Sort Key: new_avia_prices.pair_hash
  • Sort Method: quicksort Memory: 66kB
26. 40.435 96.297 ↓ 297.0 297 1

Nested Loop Semi Join (cost=0.00..6,728.34 rows=1 width=102) (actual time=21.030..96.297 rows=297 loops=1)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 489625
27. 20.858 20.858 ↓ 8,751.0 8,751 1

Seq Scan on new_avia_prices (cost=0.00..6,728.30 rows=1 width=102) (actual time=19.412..20.858 rows=8,751 loops=1)

  • Filter: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:2:1'::text)
  • Rows Removed by Filter: 170849
28. 35.004 35.004 ↓ 56.0 56 8,751

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.000..0.004 rows=56 loops=8,751)