explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KhovF : FAST

Settings
# exclusive inclusive rows x rows loops node
1. 0.092 5.420 ↓ 20.0 20 1

Sort (cost=2,089.54..2,089.54 rows=1 width=855) (actual time=5.414..5.420 rows=20 loops=1)

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

CTE data

3. 0.176 1.858 ↓ 15.7 283 1

WindowAgg (cost=1,993.42..1,994.14 rows=18 width=105) (actual time=1.668..1.858 rows=283 loops=1)

4. 0.200 1.682 ↓ 15.7 283 1

Sort (cost=1,993.42..1,993.46 rows=18 width=102) (actual time=1.656..1.682 rows=283 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: 64kB
5. 0.418 1.482 ↓ 15.7 283 1

Hash Join (cost=1,970.14..1,993.04 rows=18 width=102) (actual time=1.029..1.482 rows=283 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
6. 0.434 0.697 ↑ 3.2 283 1

HashAggregate (cost=1,073.58..1,082.56 rows=898 width=37) (actual time=0.629..0.697 rows=283 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
7. 0.150 0.263 ↑ 1.0 900 1

Bitmap Heap Scan on new_avia_prices new_avia_prices_1 (cost=127.52..1,069.00 rows=917 width=37) (actual time=0.117..0.263 rows=900 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1556323200000:0:3:5024:4312:1:0:0'::text)
  • Heap Blocks: exact=27
8. 0.113 0.113 ↑ 1.0 900 1

Bitmap Index Scan on new_avia_prices_unq (cost=0.00..127.29 rows=917 width=0) (actual time=0.113..0.113 rows=900 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556323200000:0:3:5024:4312:1:0:0'::text)
9. 0.083 0.367 ↑ 1.0 283 1

Hash (cost=892.96..892.96 rows=288 width=74) (actual time=0.367..0.367 rows=283 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
10. 0.224 0.284 ↑ 1.0 283 1

Bitmap Heap Scan on new_avia_models m_1 (cost=38.64..892.96 rows=288 width=74) (actual time=0.079..0.284 rows=283 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1556323200000:0:3:5024:4312:1:0:0'::text)
  • Heap Blocks: exact=112
11. 0.060 0.060 ↑ 1.0 283 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..38.57 rows=288 width=0) (actual time=0.060..0.060 rows=283 loops=1)

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

CTE ids

13. 0.056 2.291 ↓ 2.0 20 1

Hash Semi Join (cost=1.26..1.77 rows=10 width=516) (actual time=2.212..2.291 rows=20 loops=1)

  • Hash Cond: (data.data_hash = "ANY_subquery".rn)
14. 1.713 1.713 ↓ 15.7 283 1

CTE Scan on data (cost=0.00..0.36 rows=18 width=548) (actual time=1.670..1.713 rows=283 loops=1)

15. 0.004 0.522 ↑ 1.0 10 1

Hash (cost=1.13..1.13 rows=10 width=32) (actual time=0.522..0.522 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.518 ↑ 1.0 10 1

Subquery Scan on ANY_subquery (cost=1.01..1.13 rows=10 width=32) (actual time=0.511..0.518 rows=10 loops=1)

17. 0.002 0.513 ↑ 1.0 10 1

Limit (cost=1.01..1.03 rows=10 width=40) (actual time=0.511..0.513 rows=10 loops=1)

18. 0.058 0.511 ↑ 1.8 10 1

Sort (cost=1.01..1.05 rows=18 width=40) (actual time=0.510..0.511 rows=10 loops=1)

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
19. 0.154 0.453 ↓ 12.9 233 1

HashAggregate (cost=0.45..0.63 rows=18 width=40) (actual time=0.419..0.453 rows=233 loops=1)

  • Group Key: data_1.data_hash
20. 0.299 0.299 ↓ 15.7 283 1

CTE Scan on data data_1 (cost=0.00..0.36 rows=18 width=40) (actual time=0.001..0.299 rows=283 loops=1)

21. 0.112 5.328 ↓ 20.0 20 1

Hash Semi Join (cost=85.52..93.62 rows=1 width=855) (actual time=2.850..5.328 rows=20 loops=1)

  • Hash Cond: ((m.pair_hash)::text = (ids.pair_hash)::text)
22. 0.030 2.912 ↓ 20.0 20 1

Nested Loop (cost=85.19..93.26 rows=1 width=875) (actual time=0.525..2.912 rows=20 loops=1)

23. 2.149 2.582 ↓ 20.0 20 1

GroupAggregate (cost=84.78..84.81 rows=1 width=69) (actual time=0.497..2.582 rows=20 loops=1)

  • Group Key: new_avia_prices.pair_hash
24. 0.156 0.433 ↓ 186.0 186 1

Sort (cost=84.78..84.79 rows=1 width=102) (actual time=0.394..0.433 rows=186 loops=1)

  • Sort Key: new_avia_prices.pair_hash
  • Sort Method: quicksort Memory: 51kB
25. 0.042 0.277 ↓ 186.0 186 1

Nested Loop (cost=0.64..84.77 rows=1 width=102) (actual time=0.040..0.277 rows=186 loops=1)

26. 0.010 0.015 ↓ 2.0 20 1

HashAggregate (cost=0.23..0.33 rows=10 width=516) (actual time=0.011..0.015 rows=20 loops=1)

  • Group Key: (ids_1.pair_hash)::text
27. 0.005 0.005 ↓ 2.0 20 1

CTE Scan on ids ids_1 (cost=0.00..0.20 rows=10 width=516) (actual time=0.000..0.005 rows=20 loops=1)

28. 0.220 0.220 ↓ 9.0 9 20

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.41..8.43 rows=1 width=102) (actual time=0.008..0.011 rows=9 loops=20)

  • Index Cond: (((request_hash)::text = 'avia:1556323200000:0:3:5024:4312:1:0:0'::text) AND ((pair_hash)::text = (ids_1.pair_hash)::text))
29. 0.300 0.300 ↑ 1.0 1 20

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

  • Index Cond: (((request_hash)::text = 'avia:1556323200000:0:3:5024:4312:1:0:0'::text) AND ((pair_hash)::text = (new_avia_prices.pair_hash)::text))
30. 0.008 2.304 ↓ 2.0 20 1

Hash (cost=0.20..0.20 rows=10 width=516) (actual time=2.304..2.304 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 2.296 2.296 ↓ 2.0 20 1

CTE Scan on ids (cost=0.00..0.20 rows=10 width=516) (actual time=2.214..2.296 rows=20 loops=1)