explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NyhB : ids fast

Settings
# exclusive inclusive rows x rows loops node
1. 0.326 32.029 ↑ 10.0 53 1

Hash Join (cost=23,659.68..23,956.11 rows=529 width=33) (actual time=27.859..32.029 rows=53 loops=1)

  • Hash Cond: ((((m.data_hash)::text || ((min(new_avia_prices.price)))::text)) = "ANY_subquery".rn)
2. 3.260 11.422 ↓ 3.0 3,208 1

Hash Join (cost=11,603.63..11,884.99 rows=1,058 width=105) (actual time=7.568..11.422 rows=3,208 loops=1)

  • Hash Cond: ((new_avia_prices.pair_hash)::text = (m.pair_hash)::text)
3. 2.611 4.816 ↑ 2.1 3,208 1

HashAggregate (cost=4,327.54..4,394.21 rows=6,667 width=37) (actual time=4.197..4.816 rows=3,208 loops=1)

  • Group Key: new_avia_prices.pair_hash
4. 0.963 2.205 ↓ 1.0 8,379 1

Bitmap Heap Scan on new_avia_prices (cost=737.22..4,285.73 rows=8,361 width=37) (actual time=1.270..2.205 rows=8,379 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
  • Heap Blocks: exact=210
5. 1.242 1.242 ↓ 1.0 8,379 1

Bitmap Index Scan on new_avia_prices_unq (cost=0.00..735.13 rows=8,361 width=0) (actual time=1.242..1.242 rows=8,379 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
6. 0.746 3.346 ↑ 1.0 3,208 1

Hash (cost=7,235.18..7,235.18 rows=3,273 width=82) (actual time=3.346..3.346 rows=3,208 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 396kB
7. 2.229 2.600 ↑ 1.0 3,208 1

Bitmap Heap Scan on new_avia_models m (cost=225.78..7,235.18 rows=3,273 width=82) (actual time=0.438..2.600 rows=3,208 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
  • Heap Blocks: exact=643
8. 0.371 0.371 ↑ 1.0 3,208 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..224.96 rows=3,273 width=0) (actual time=0.371..0.371 rows=3,208 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
9. 0.005 20.281 ↑ 1.0 10 1

Hash (cost=12,055.92..12,055.92 rows=10 width=32) (actual time=20.280..20.281 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.001 20.276 ↑ 1.0 10 1

Subquery Scan on ANY_subquery (cost=12,055.80..12,055.92 rows=10 width=32) (actual time=20.269..20.276 rows=10 loops=1)

11. 0.006 20.275 ↑ 1.0 10 1

Limit (cost=12,055.80..12,055.82 rows=10 width=40) (actual time=20.269..20.275 rows=10 loops=1)

12. 0.084 20.269 ↑ 20.0 10 1

Sort (cost=12,055.80..12,056.30 rows=200 width=40) (actual time=20.267..20.269 rows=10 loops=1)

  • Sort Key: (min(a.row_number))
  • Sort Method: top-N heapsort Memory: 25kB
13. 0.406 20.185 ↓ 2.2 438 1

GroupAggregate (cost=12,041.54..12,051.47 rows=200 width=40) (actual time=19.536..20.185 rows=438 loops=1)

  • Group Key: a.data_hash
14. 3.926 19.779 ↓ 3.0 3,208 1

Sort (cost=12,041.54..12,044.18 rows=1,058 width=40) (actual time=19.529..19.779 rows=3,208 loops=1)

  • Sort Key: a.data_hash
  • Sort Method: quicksort Memory: 347kB
15. 0.384 15.853 ↓ 3.0 3,208 1

Subquery Scan on a (cost=11,930.20..11,988.39 rows=1,058 width=40) (actual time=12.849..15.853 rows=3,208 loops=1)

16. 1.925 15.469 ↓ 3.0 3,208 1

WindowAgg (cost=11,930.20..11,977.81 rows=1,058 width=588) (actual time=12.848..15.469 rows=3,208 loops=1)

17. 2.184 13.544 ↓ 3.0 3,208 1

Sort (cost=11,930.20..11,932.84 rows=1,058 width=69) (actual time=12.835..13.544 rows=3,208 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
18. 3.582 11.360 ↓ 3.0 3,208 1

Hash Join (cost=11,603.63..11,877.05 rows=1,058 width=69) (actual time=6.880..11.360 rows=3,208 loops=1)

  • Hash Cond: ((new_avia_prices_1.pair_hash)::text = (m_1.pair_hash)::text)
19. 3.037 4.870 ↑ 2.1 3,208 1

HashAggregate (cost=4,327.54..4,394.21 rows=6,667 width=37) (actual time=3.949..4.870 rows=3,208 loops=1)

  • Group Key: new_avia_prices_1.pair_hash
20. 0.932 1.833 ↓ 1.0 8,379 1

Bitmap Heap Scan on new_avia_prices new_avia_prices_1 (cost=737.22..4,285.73 rows=8,361 width=37) (actual time=0.925..1.833 rows=8,379 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
  • Heap Blocks: exact=210
21. 0.901 0.901 ↓ 1.0 8,379 1

Bitmap Index Scan on new_avia_prices_unq (cost=0.00..735.13 rows=8,361 width=0) (actual time=0.901..0.901 rows=8,379 loops=1)

  • Index Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
22. 0.587 2.908 ↑ 1.0 3,208 1

Hash (cost=7,235.18..7,235.18 rows=3,273 width=82) (actual time=2.908..2.908 rows=3,208 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 399kB
23. 1.956 2.321 ↑ 1.0 3,208 1

Bitmap Heap Scan on new_avia_models m_1 (cost=225.78..7,235.18 rows=3,273 width=82) (actual time=0.431..2.321 rows=3,208 loops=1)

  • Recheck Cond: ((request_hash)::text = 'avia:1555804800000:1557532800000:3:5024:4312:1:1:0'::text)
  • Heap Blocks: exact=643
24. 0.365 0.365 ↑ 1.0 3,208 1

Bitmap Index Scan on avia_pair_hash_unq (cost=0.00..224.96 rows=3,273 width=0) (actual time=0.364..0.365 rows=3,208 loops=1)

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