explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QkCF : Optimization for: with some tuning; plan #vYRr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.229 3,877.366 ↓ 55.0 55 1

Sort (cost=32.90..32.90 rows=1 width=1,386) (actual time=3,877.355..3,877.366 rows=55 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: 147kB
2.          

CTE data

3. 1.791 20.561 ↓ 2,870.0 2,870 1

WindowAgg (cost=16.34..16.39 rows=1 width=105) (actual time=18.187..20.561 rows=2,870 loops=1)

4. 2.197 18.770 ↓ 2,870.0 2,870 1

Sort (cost=16.34..16.34 rows=1 width=102) (actual time=18.174..18.770 rows=2,870 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: 500kB
5. 4.436 16.573 ↓ 2,870.0 2,870 1

Hash Join (cost=8.87..16.33 rows=1 width=102) (actual time=4.458..16.573 rows=2,870 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
6. 2.699 7.752 ↓ 2,870.0 2,870 1

GroupAggregate (cost=0.42..7.84 rows=1 width=37) (actual time=0.050..7.752 rows=2,870 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
7. 5.053 5.053 ↓ 7,685.0 7,685 1

Index Scan using new_avia_prices_unq on new_avia_prices new_avia_prices_1 (cost=0.42..7.82 rows=1 width=37) (actual time=0.040..5.053 rows=7,685 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
8. 0.836 4.385 ↓ 2,870.0 2,870 1

Hash (cost=8.44..8.44 rows=1 width=82) (actual time=4.385..4.385 rows=2,870 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 358kB
9. 2.847 3.549 ↓ 2,870.0 2,870 1

Bitmap Heap Scan on new_avia_models m_1 (cost=4.42..8.44 rows=1 width=82) (actual time=0.787..3.549 rows=2,870 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
  • Heap Blocks: exact=574
10. 0.702 0.702 ↓ 2,870.0 2,870 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..4.42 rows=1 width=0) (actual time=0.702..0.702 rows=2,870 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
11.          

CTE ids

12. 5.814 33.190 ↓ 55.0 55 1

Nested Loop Semi Join (cost=0.04..0.09 rows=1 width=516) (actual time=23.224..33.190 rows=55 loops=1)

  • Join Filter: (data.data_hash = data_1.data_hash)
  • Rows Removed by Join Filter: 28434
13. 18.766 18.766 ↓ 2,870.0 2,870 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=548) (actual time=18.190..18.766 rows=2,870 loops=1)

14. 0.000 8.610 ↓ 10.0 10 2,870

Limit (cost=0.04..0.05 rows=1 width=40) (actual time=0.002..0.003 rows=10 loops=2,870)

15. 3.600 8.610 ↓ 10.0 10 2,870

Sort (cost=0.04..0.05 rows=1 width=40) (actual time=0.002..0.003 rows=10 loops=2,870)

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
16. 1.187 5.010 ↓ 425.0 425 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=4.863..5.010 rows=425 loops=1)

  • Group Key: data_1.data_hash
17. 3.823 3.823 ↓ 2,870.0 2,870 1

CTE Scan on data data_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..3.823 rows=2,870 loops=1)

18. 1.217 3,877.137 ↓ 55.0 55 1

Nested Loop (cost=4.88..16.41 rows=1 width=1,386) (actual time=67.171..3,877.137 rows=55 loops=1)

  • Join Filter: ((m.pair_hash)::text = (new_avia_prices.pair_hash)::text)
  • Rows Removed by Join Filter: 2970
19. 0.720 35.435 ↓ 55.0 55 1

Hash Semi Join (cost=4.46..8.47 rows=1 width=1,857) (actual time=33.785..35.435 rows=55 loops=1)

  • Hash Cond: ((m.pair_hash)::text = (ids.pair_hash)::text)
20. 1.022 1.493 ↓ 2,870.0 2,870 1

Bitmap Heap Scan on new_avia_models m (cost=4.42..8.44 rows=1 width=1,341) (actual time=0.545..1.493 rows=2,870 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
  • Heap Blocks: exact=574
21. 0.471 0.471 ↓ 2,870.0 2,870 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..4.42 rows=1 width=0) (actual time=0.471..0.471 rows=2,870 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
22. 0.014 33.222 ↓ 55.0 55 1

Hash (cost=0.02..0.02 rows=1 width=516) (actual time=33.222..33.222 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
23. 33.208 33.208 ↓ 55.0 55 1

CTE Scan on ids (cost=0.00..0.02 rows=1 width=516) (actual time=23.226..33.208 rows=55 loops=1)

24. 88.000 3,840.485 ↓ 55.0 55 55

GroupAggregate (cost=0.42..7.89 rows=1 width=69) (actual time=4.183..69.827 rows=55 loops=55)

  • Group Key: new_avia_prices.pair_hash
25. 2,317.260 3,752.485 ↓ 250.0 250 55

Nested Loop Semi Join (cost=0.42..7.86 rows=1 width=102) (actual time=0.129..68.227 rows=250 loops=55)

  • Join Filter: ((new_avia_prices.pair_hash)::text = (ids_1.pair_hash)::text)
  • Rows Removed by Join Filter: 414991
26. 167.200 167.200 ↓ 7,685.0 7,685 55

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.42..7.82 rows=1 width=102) (actual time=0.017..3.040 rows=7,685 loops=55)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:0:1'::text)
27. 1,268.025 1,268.025 ↓ 54.0 54 422,675

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.000..0.003 rows=54 loops=422,675)