explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iuw3

Settings
# exclusive inclusive rows x rows loops node
1. 0.328 11.622 ↓ 143.0 143 1

Sort (cost=2,220.71..2,220.72 rows=1 width=832) (actual time=11.586..11.622 rows=143 loops=1)

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

CTE data

3. 0.190 2.276 ↓ 20.2 283 1

WindowAgg (cost=2,090.90..2,091.46 rows=14 width=105) (actual time=2.034..2.276 rows=283 loops=1)

4. 0.194 2.086 ↓ 20.2 283 1

Sort (cost=2,090.90..2,090.94 rows=14 width=102) (actual time=2.024..2.086 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.314 1.892 ↓ 20.2 283 1

Hash Join (cost=2,068.34..2,090.64 rows=14 width=102) (actual time=1.533..1.892 rows=283 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
6. 0.368 0.846 ↑ 3.1 283 1

HashAggregate (cost=1,177.36..1,186.14 rows=878 width=37) (actual time=0.784..0.846 rows=283 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
7. 0.284 0.478 ↓ 1.0 900 1

Bitmap Heap Scan on new_avia_prices new_avia_prices_1 (cost=63.34..1,172.89 rows=894 width=37) (actual time=0.204..0.478 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.194 0.194 ↓ 1.0 900 1

Bitmap Index Scan on new_avia_prices_unq (cost=0.00..63.12 rows=894 width=0) (actual time=0.194..0.194 rows=900 loops=1)

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

Hash (cost=887.44..887.44 rows=283 width=74) (actual time=0.732..0.732 rows=283 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
10. 0.529 0.648 ↑ 1.0 283 1

Bitmap Heap Scan on new_avia_models m_1 (cost=18.61..887.44 rows=283 width=74) (actual time=0.143..0.648 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.119 0.119 ↑ 1.0 283 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..18.54 rows=283 width=0) (actual time=0.119..0.119 rows=283 loops=1)

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

CTE ids

13. 0.112 2.902 ↓ 10.2 143 1

Hash Semi Join (cost=1.11..1.58 rows=14 width=516) (actual time=2.772..2.902 rows=143 loops=1)

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

CTE Scan on data (cost=0.00..0.28 rows=14 width=548) (actual time=2.035..2.077 rows=283 loops=1)

15. 0.029 0.713 ↓ 7.1 100 1

Hash (cost=0.93..0.93 rows=14 width=32) (actual time=0.713..0.713 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
16. 0.025 0.684 ↓ 7.1 100 1

Subquery Scan on ANY_subquery (cost=0.76..0.93 rows=14 width=32) (actual time=0.624..0.684 rows=100 loops=1)

17. 0.021 0.659 ↓ 7.1 100 1

Limit (cost=0.76..0.79 rows=14 width=40) (actual time=0.624..0.659 rows=100 loops=1)

18. 0.122 0.638 ↓ 7.1 100 1

Sort (cost=0.76..0.79 rows=14 width=40) (actual time=0.622..0.638 rows=100 loops=1)

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 32kB
19. 0.152 0.516 ↓ 16.6 233 1

HashAggregate (cost=0.35..0.49 rows=14 width=40) (actual time=0.472..0.516 rows=233 loops=1)

  • Group Key: data_1.data_hash
20. 0.364 0.364 ↓ 20.2 283 1

CTE Scan on data data_1 (cost=0.00..0.28 rows=14 width=40) (actual time=0.000..0.364 rows=283 loops=1)

21. 0.278 11.294 ↓ 143.0 143 1

Hash Semi Join (cost=119.56..127.66 rows=1 width=832) (actual time=5.423..11.294 rows=143 loops=1)

  • Hash Cond: ((m.pair_hash)::text = (ids.pair_hash)::text)
22. 0.153 8.032 ↓ 143.0 143 1

Nested Loop (cost=119.11..127.17 rows=1 width=852) (actual time=2.425..8.032 rows=143 loops=1)

23. 4.256 6.735 ↓ 143.0 143 1

GroupAggregate (cost=118.70..118.72 rows=1 width=69) (actual time=2.411..6.735 rows=143 loops=1)

  • Group Key: new_avia_prices.pair_hash
24. 0.796 2.479 ↓ 704.0 704 1

Sort (cost=118.70..118.70 rows=1 width=102) (actual time=2.326..2.479 rows=704 loops=1)

  • Sort Key: new_avia_prices.pair_hash
  • Sort Method: quicksort Memory: 124kB
25. 0.277 1.683 ↓ 704.0 704 1

Nested Loop (cost=0.73..118.69 rows=1 width=102) (actual time=0.103..1.683 rows=704 loops=1)

26. 0.082 0.119 ↓ 10.2 143 1

HashAggregate (cost=0.32..0.46 rows=14 width=516) (actual time=0.084..0.119 rows=143 loops=1)

  • Group Key: (ids_1.pair_hash)::text
27. 0.037 0.037 ↓ 10.2 143 1

CTE Scan on ids ids_1 (cost=0.00..0.28 rows=14 width=516) (actual time=0.001..0.037 rows=143 loops=1)

28. 1.287 1.287 ↓ 5.0 5 143

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.009 rows=5 loops=143)

  • 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. 1.144 1.144 ↑ 1.0 1 143

Index Scan using avia_pair_hash_unq on new_avia_models m (cost=0.41..8.43 rows=1 width=783) (actual time=0.008..0.008 rows=1 loops=143)

  • 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.032 2.984 ↓ 10.2 143 1

Hash (cost=0.28..0.28 rows=14 width=516) (actual time=2.984..2.984 rows=143 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
31. 2.952 2.952 ↓ 10.2 143 1

CTE Scan on ids (cost=0.00..0.28 rows=14 width=516) (actual time=2.774..2.952 rows=143 loops=1)

Planning time : 1.588 ms