explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lZrY

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.154 271.034 ↓ 57.0 57 1

Sort (cost=12,816.79..12,816.80 rows=1 width=1,381) (actual time=271.020..271.034 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.131 44.067 ↓ 3,231.0 3,231 1

WindowAgg (cost=6,408.28..6,408.33 rows=1 width=105) (actual time=42.677..44.067 rows=3,231 loops=1)

4. 1.530 42.936 ↓ 3,231.0 3,231 1

Sort (cost=6,408.28..6,408.29 rows=1 width=102) (actual time=42.668..42.936 rows=3,231 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: 551kB
5. 4.510 41.406 ↓ 3,231.0 3,231 1

Hash Join (cost=6,404.23..6,408.27 rows=1 width=102) (actual time=35.371..41.406 rows=3,231 loops=1)

  • Hash Cond: ((m.pair_hash)::text = (p.pair_hash)::text)
6. 1.621 2.082 ↓ 3,231.0 3,231 1

Bitmap Heap Scan on new_avia_models m (cost=4.43..8.44 rows=1 width=82) (actual time=0.529..2.082 rows=3,231 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:1:1'::text)
  • Heap Blocks: exact=647
7. 0.461 0.461 ↓ 3,231.0 3,231 1

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

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:1:1'::text)
8. 0.584 34.814 ↓ 3,231.0 3,231 1

Hash (cost=6,399.79..6,399.79 rows=1 width=37) (actual time=34.814..34.814 rows=3,231 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 250kB
9. 0.256 34.230 ↓ 3,231.0 3,231 1

Subquery Scan on p (cost=6,399.76..6,399.79 rows=1 width=37) (actual time=31.720..34.230 rows=3,231 loops=1)

10. 1.574 33.974 ↓ 3,231.0 3,231 1

GroupAggregate (cost=6,399.76..6,399.78 rows=1 width=37) (actual time=31.720..33.974 rows=3,231 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
11. 11.038 32.400 ↓ 8,255.0 8,255 1

Sort (cost=6,399.76..6,399.77 rows=1 width=37) (actual time=31.713..32.400 rows=8,255 loops=1)

  • Sort Key: new_avia_prices_1.pair_hash
  • Sort Method: quicksort Memory: 1029kB
12. 21.362 21.362 ↓ 8,255.0 8,255 1

Seq Scan on new_avia_prices new_avia_prices_1 (cost=0.00..6,399.75 rows=1 width=37) (actual time=18.940..21.362 rows=8,255 loops=1)

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

CTE ids

14. 4.009 53.497 ↓ 57.0 57 1

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

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 32039
15. 43.026 43.026 ↓ 3,231.0 3,231 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=42.680..43.026 rows=3,231 loops=1)

16. 0.000 6.462 ↓ 10.0 10 3,231

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

17. 3.404 6.462 ↓ 10.0 10 3,231

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

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
18. 0.741 3.058 ↓ 453.0 453 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=2.927..3.058 rows=453 loops=1)

  • Group Key: data_1.data_hash
19. 2.317 2.317 ↓ 3,231.0 3,231 1

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

20. 0.686 270.880 ↓ 57.0 57 1

Nested Loop (cost=6,400.22..6,408.36 rows=1 width=1,381) (actual time=146.836..270.880 rows=57 loops=1)

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

Nested Loop Semi Join (cost=0.42..8.48 rows=1 width=1,852) (actual time=53.603..87.509 rows=57 loops=1)

  • Join Filter: ((m2.pair_hash)::text = (ids.pair_hash)::text)
  • Rows Removed by Join Filter: 182514
22. 2.817 2.817 ↓ 3,231.0 3,231 1

Index Scan using avia_pair_hash_unq on new_avia_models m2 (cost=0.42..8.44 rows=1 width=1,336) (actual time=0.032..2.817 rows=3,231 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:2:1:1'::text)
23. 64.620 64.620 ↓ 57.0 57 3,231

CTE Scan on ids (cost=0.00..0.02 rows=1 width=516) (actual time=0.014..0.020 rows=57 loops=3,231)

24. 88.122 182.685 ↓ 57.0 57 57

GroupAggregate (cost=6,399.80..6,399.83 rows=1 width=69) (actual time=1.668..3.205 rows=57 loops=57)

  • Group Key: new_avia_prices.pair_hash
25. 1.562 94.563 ↓ 272.0 272 57

Sort (cost=6,399.80..6,399.81 rows=1 width=102) (actual time=1.635..1.659 rows=272 loops=57)

  • Sort Key: new_avia_prices.pair_hash
  • Sort Method: quicksort Memory: 63kB
26. 40.200 93.001 ↓ 272.0 272 1

Nested Loop Semi Join (cost=0.00..6,399.79 rows=1 width=102) (actual time=20.053..93.001 rows=272 loops=1)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 462124
27. 19.781 19.781 ↓ 8,255.0 8,255 1

Seq Scan on new_avia_prices (cost=0.00..6,399.75 rows=1 width=102) (actual time=18.423..19.781 rows=8,255 loops=1)

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

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,255)

Planning time : 0.627 ms
Execution time : 271.209 ms