explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VGQf

Settings
# exclusive inclusive rows x rows loops node
1. 4.276 59.944 ↓ 53.0 53 1

Nested Loop Semi Join (cost=25.30..32.81 rows=1 width=33) (actual time=36.439..59.944 rows=53 loops=1)

  • Join Filter: ((((m.data_hash)::text || ((min(new_avia_prices.price)))::text)) = a.data_hash)
  • Rows Removed by Join Filter: 31839
2. 4.965 17.160 ↓ 3,209.0 3,209 1

Hash Join (cost=8.87..16.34 rows=1 width=105) (actual time=5.328..17.160 rows=3,209 loops=1)

  • Hash Cond: ((new_avia_prices.pair_hash)::text = (m.pair_hash)::text)
3. 2.658 7.094 ↓ 3,209.0 3,209 1

GroupAggregate (cost=0.42..7.84 rows=1 width=37) (actual time=0.199..7.094 rows=3,209 loops=1)

  • Group Key: new_avia_prices.pair_hash
4. 4.436 4.436 ↓ 8,852.0 8,852 1

Index Scan using new_avia_prices_unq on new_avia_prices (cost=0.42..7.82 rows=1 width=37) (actual time=0.054..4.436 rows=8,852 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
5. 0.965 5.101 ↓ 3,209.0 3,209 1

Hash (cost=8.44..8.44 rows=1 width=82) (actual time=5.101..5.101 rows=3,209 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 396kB
6. 3.270 4.136 ↓ 3,209.0 3,209 1

Bitmap Heap Scan on new_avia_models m (cost=4.42..8.44 rows=1 width=82) (actual time=0.967..4.136 rows=3,209 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
  • Heap Blocks: exact=642
7. 0.866 0.866 ↓ 3,209.0 3,209 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..4.42 rows=1 width=0) (actual time=0.865..0.866 rows=3,209 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
8. 6.418 38.508 ↓ 10.0 10 3,209

Limit (cost=16.44..16.44 rows=1 width=40) (actual time=0.010..0.012 rows=10 loops=3,209)

9. 1.077 32.090 ↓ 10.0 10 3,209

Sort (cost=16.44..16.44 rows=1 width=40) (actual time=0.010..0.010 rows=10 loops=3,209)

  • Sort Key: (min(a.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
10. 0.679 31.013 ↓ 445.0 445 1

GroupAggregate (cost=16.41..16.43 rows=1 width=40) (actual time=30.002..31.013 rows=445 loops=1)

  • Group Key: a.data_hash
11. 5.058 30.334 ↓ 3,209.0 3,209 1

Sort (cost=16.41..16.41 rows=1 width=40) (actual time=29.996..30.334 rows=3,209 loops=1)

  • Sort Key: a.data_hash
  • Sort Method: quicksort Memory: 347kB
12. 0.335 25.276 ↓ 3,209.0 3,209 1

Subquery Scan on a (cost=16.34..16.40 rows=1 width=40) (actual time=22.820..25.276 rows=3,209 loops=1)

13. 1.698 24.941 ↓ 3,209.0 3,209 1

WindowAgg (cost=16.34..16.39 rows=1 width=588) (actual time=22.820..24.941 rows=3,209 loops=1)

14. 2.168 23.243 ↓ 3,209.0 3,209 1

Sort (cost=16.34..16.34 rows=1 width=69) (actual time=22.808..23.243 rows=3,209 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: 347kB
15. 5.825 21.075 ↓ 3,209.0 3,209 1

Hash Join (cost=8.87..16.33 rows=1 width=69) (actual time=4.806..21.075 rows=3,209 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
16. 3.370 10.408 ↓ 3,209.0 3,209 1

GroupAggregate (cost=0.42..7.84 rows=1 width=37) (actual time=0.026..10.408 rows=3,209 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
17. 7.038 7.038 ↓ 8,852.0 8,852 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.020..7.038 rows=8,852 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
18. 1.191 4.842 ↓ 3,209.0 3,209 1

Hash (cost=8.44..8.44 rows=1 width=82) (actual time=4.756..4.842 rows=3,209 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 399kB
19. 3.071 3.651 ↓ 3,209.0 3,209 1

Bitmap Heap Scan on new_avia_models m_1 (cost=4.42..8.44 rows=1 width=82) (actual time=0.600..3.651 rows=3,209 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
  • Heap Blocks: exact=642
20. 0.580 0.580 ↓ 3,209.0 3,209 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..4.42 rows=1 width=0) (actual time=0.500..0.580 rows=3,209 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:2:0'::text)
Planning time : 0.730 ms
Execution time : 60.065 ms