explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qS1r

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 29.564 ↑ 24.9 13 1

Sort (cost=12,043.95..12,044.76 rows=324 width=1,424) (actual time=29.563..29.564 rows=13 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: 53kB
2.          

CTE data

3. 1.139 26.490 ↓ 3.1 3,279 1

WindowAgg (cost=8,286.35..8,334.28 rows=1,065 width=105) (actual time=25.095..26.490 rows=3,279 loops=1)

4. 1.701 25.351 ↓ 3.1 3,279 1

Sort (cost=8,286.35..8,289.02 rows=1,065 width=102) (actual time=25.086..25.351 rows=3,279 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: 558kB
5. 4.323 23.650 ↓ 3.1 3,279 1

Hash Join (cost=8,020.44..8,232.80 rows=1,065 width=102) (actual time=18.253..23.650 rows=3,279 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
6. 3.423 14.279 ↑ 2.1 3,279 1

HashAggregate (cost=2,672.49..2,741.22 rows=6,873 width=37) (actual time=13.180..14.279 rows=3,279 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
7. 10.856 10.856 ↓ 1.0 8,461 1

Seq Scan on new_avia_prices new_avia_prices_1 (cost=0.00..2,630.78 rows=8,343 width=37) (actual time=9.260..10.856 rows=8,461 loops=1)

  • Filter: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
  • Rows Removed by Filter: 61841
8. 1.205 5.048 ↑ 1.0 3,279 1

Hash (cost=5,306.96..5,306.96 rows=3,279 width=82) (actual time=5.047..5.048 rows=3,279 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 404kB
9. 3.409 3.843 ↑ 1.0 3,279 1

Bitmap Heap Scan on new_avia_models m_1 (cost=265.82..5,306.96 rows=3,279 width=82) (actual time=0.508..3.843 rows=3,279 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
  • Heap Blocks: exact=656
10. 0.434 0.434 ↑ 1.0 3,279 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..265.00 rows=3,279 width=0) (actual time=0.434..0.434 rows=3,279 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text)
11.          

CTE ids

12. 0.288 28.882 ↑ 40.9 13 1

Hash Join (cost=33.20..59.02 rows=532 width=516) (actual time=28.273..28.882 rows=13 loops=1)

  • Hash Cond: (data.data_hash = "ANY_subquery".rn)
13. 25.425 25.425 ↓ 3.1 3,279 1

CTE Scan on data (cost=0.00..21.30 rows=1,065 width=548) (actual time=25.097..25.425 rows=3,279 loops=1)

14. 0.005 3.169 ↑ 1.0 10 1

Hash (cost=33.07..33.07 rows=10 width=32) (actual time=3.169..3.169 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.002 3.164 ↑ 1.0 10 1

Subquery Scan on ANY_subquery (cost=32.95..33.07 rows=10 width=32) (actual time=3.163..3.164 rows=10 loops=1)

16. 0.000 3.162 ↑ 1.0 10 1

Limit (cost=32.95..32.97 rows=10 width=40) (actual time=3.162..3.162 rows=10 loops=1)

17. 0.092 3.162 ↑ 20.0 10 1

Sort (cost=32.95..33.45 rows=200 width=40) (actual time=3.162..3.162 rows=10 loops=1)

  • Sort Key: (min(data_1.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
18. 0.782 3.070 ↓ 3.4 672 1

HashAggregate (cost=26.63..28.63 rows=200 width=40) (actual time=3.002..3.070 rows=672 loops=1)

  • Group Key: data_1.data_hash
19. 2.288 2.288 ↓ 3.1 3,279 1

CTE Scan on data data_1 (cost=0.00..21.30 rows=1,065 width=40) (actual time=0.001..2.288 rows=3,279 loops=1)

20. 0.032 29.547 ↑ 24.9 13 1

Hash Join (cost=3,457.31..3,637.14 rows=324 width=1,424) (actual time=29.213..29.547 rows=13 loops=1)

  • Hash Cond: ((new_avia_prices.pair_hash)::text = (m.pair_hash)::text)
21. 0.354 0.501 ↑ 320.9 13 1

GroupAggregate (cost=1,874.34..1,978.64 rows=4,172 width=69) (actual time=0.187..0.501 rows=13 loops=1)

  • Group Key: new_avia_prices.pair_hash
22. 0.031 0.147 ↑ 65.2 64 1

Sort (cost=1,874.34..1,884.77 rows=4,172 width=102) (actual time=0.142..0.147 rows=64 loops=1)

  • Sort Key: new_avia_prices.pair_hash
  • Sort Method: quicksort Memory: 34kB
23. 0.005 0.116 ↑ 65.2 64 1

Nested Loop (cost=12.39..1,623.47 rows=4,172 width=102) (actual time=0.016..0.116 rows=64 loops=1)

24. 0.006 0.007 ↑ 15.4 13 1

HashAggregate (cost=11.97..13.97 rows=200 width=516) (actual time=0.007..0.007 rows=13 loops=1)

  • Group Key: (ids_1.pair_hash)::text
25. 0.001 0.001 ↑ 40.9 13 1

CTE Scan on ids ids_1 (cost=0.00..10.64 rows=532 width=516) (actual time=0.001..0.001 rows=13 loops=1)

26. 0.104 0.104 ↓ 5.0 5 13

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.42..8.04 rows=1 width=102) (actual time=0.006..0.008 rows=5 loops=13)

  • Index Cond: (((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text) AND ((pair_hash)::text = (ids_1.pair_hash)::text))
27. 0.010 29.014 ↑ 126.2 13 1

Hash (cost=1,562.47..1,562.47 rows=1,640 width=1,895) (actual time=29.014..29.014 rows=13 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 33kB
28. 0.016 29.004 ↑ 126.2 13 1

Nested Loop (cost=12.38..1,562.47 rows=1,640 width=1,895) (actual time=28.915..29.004 rows=13 loops=1)

29. 0.009 28.897 ↑ 15.4 13 1

HashAggregate (cost=11.97..13.97 rows=200 width=516) (actual time=28.893..28.897 rows=13 loops=1)

  • Group Key: (ids.pair_hash)::text
30. 28.888 28.888 ↑ 40.9 13 1

CTE Scan on ids (cost=0.00..10.64 rows=532 width=516) (actual time=28.276..28.888 rows=13 loops=1)

31. 0.091 0.091 ↑ 1.0 1 13

Index Scan using avia_pair_hash_unq on new_avia_models m (cost=0.41..7.73 rows=1 width=1,379) (actual time=0.007..0.007 rows=1 loops=13)

  • Index Cond: (((request_hash)::text = 'avia:1556582400000:1556928000000:3:5024:4312:2:1:0'::text) AND ((pair_hash)::text = (ids.pair_hash)::text))