explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ONdYt

Settings
# exclusive inclusive rows x rows loops node
1. 252.647 20,640.537 ↓ 2,036.8 183,310 1

Nested Loop (cost=236,226.45..328,665.88 rows=90 width=618) (actual time=18,197.152..20,640.537 rows=183,310 loops=1)

  • Output: t_allo_availability.unique_id, t_allo_availability.date_time, t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t_allo_ids.site_id, t_allo_ids.cat_1, t_allo_ids.cat_2, t_allo_ids.cat_3, t_allo_ids.cat_4
  • Inner Unique: true
  • Buffers: shared hit=5373238 read=61079, temp read=95502 written=95416
2.          

Initplan (forNested Loop)

3. 0.002 0.741 ↑ 1.0 1 1

Result (cost=1.08..1.09 rows=1 width=8) (actual time=0.740..0.741 rows=1 loops=1)

  • Output: $0
  • Buffers: shared hit=2 read=2
4.          

Initplan (forResult)

5. 0.002 0.739 ↑ 1.0 1 1

Limit (cost=0.43..1.08 rows=1 width=8) (actual time=0.737..0.739 rows=1 loops=1)

  • Output: t_allo_availability_1.date_time
  • Buffers: shared hit=2 read=2
6. 0.737 0.737 ↑ 16,417,697.0 1 1

Index Only Scan Backward using t_allo_availability_datetime_index on public.t_allo_availability t_allo_availability_1 (cost=0.43..10,569,928.44 rows=16,417,697 width=8) (actual time=0.736..0.737 rows=1 loops=1)

  • Output: t_allo_availability_1.date_time
  • Index Cond: (t_allo_availability_1.date_time IS NOT NULL)
  • Heap Fetches: 1
  • Buffers: shared hit=2 read=2
7. 649.462 18,737.359 ↓ 2,036.8 183,310 1

Hash Join (cost=236,224.94..328,185.15 rows=90 width=493) (actual time=18,014.992..18,737.359 rows=183,310 loops=1)

  • Output: t_allo_availability.unique_id, t_allo_availability.date_time, t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t_allo_description.unique_id
  • Hash Cond: (t_allo_availability.unique_id = t1.unique_id)
  • Buffers: shared hit=4646681 read=53905, temp read=95502 written=95416
8. 98.273 250.104 ↑ 1.1 183,310 1

Bitmap Heap Scan on public.t_allo_availability (cost=3,709.42..94,926.97 rows=197,804 width=12) (actual time=151.960..250.104 rows=183,310 loops=1)

  • Output: t_allo_availability.date_time, t_allo_availability.unique_id
  • Recheck Cond: (t_allo_availability.date_time = $1)
  • Heap Blocks: exact=992
  • Buffers: shared hit=5 read=1627
9. 151.831 151.831 ↑ 1.1 183,310 1

Bitmap Index Scan on t_allo_availability_datetime_index (cost=0.00..3,659.96 rows=197,804 width=0) (actual time=151.831..151.831 rows=183,310 loops=1)

  • Index Cond: (t_allo_availability.date_time = $1)
  • Buffers: shared hit=4 read=636
10. 307.962 17,837.793 ↓ 2,885.9 288,591 1

Hash (cost=232,514.27..232,514.27 rows=100 width=481) (actual time=17,837.793..17,837.793 rows=288,591 loops=1)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t_allo_description.unique_id
  • Buckets: 16384 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3969kB
  • Buffers: shared hit=4646676 read=52278, temp read=68811 written=84824
11. 411.992 17,529.831 ↓ 2,885.9 288,591 1

Merge Join (cost=223,307.34..232,514.27 rows=100 width=481) (actual time=14,632.149..17,529.831 rows=288,591 loops=1)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t_allo_description.unique_id
  • Merge Cond: (t_allo_description.unique_id = t1.unique_id)
  • Join Filter: (t2.date_time = (max(t_allo_description.date_time)))
  • Rows Removed by Join Filter: 152648
  • Buffers: shared hit=4646676 read=52278, temp read=68811 written=68851
12. 226.849 1,617.506 ↓ 1.2 288,591 1

GroupAggregate (cost=76,845.70..82,608.67 rows=245,368 width=12) (actual time=1,213.902..1,617.506 rows=288,591 loops=1)

  • Output: t_allo_description.unique_id, max(t_allo_description.date_time)
  • Group Key: t_allo_description.unique_id
  • Buffers: shared hit=17013 read=6509, temp read=1406 written=1413
13. 452.376 1,390.657 ↑ 1.0 441,239 1

Sort (cost=76,845.70..77,948.79 rows=441,239 width=12) (actual time=1,213.892..1,390.657 rows=441,239 loops=1)

  • Output: t_allo_description.unique_id, t_allo_description.date_time
  • Sort Key: t_allo_description.unique_id
  • Sort Method: external merge Disk: 11248kB
  • Buffers: shared hit=17013 read=6509, temp read=1406 written=1413
14. 938.281 938.281 ↑ 1.0 441,239 1

Seq Scan on public.t_allo_description (cost=0.00..27,934.39 rows=441,239 width=12) (actual time=0.490..938.281 rows=441,239 loops=1)

  • Output: t_allo_description.unique_id, t_allo_description.date_time
  • Buffers: shared hit=17013 read=6509
15. 213.805 15,500.333 ↓ 43.2 441,239 1

Materialize (cost=146,461.64..146,512.71 rows=10,213 width=485) (actual time=13,418.232..15,500.333 rows=441,239 loops=1)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t2.date_time
  • Buffers: shared hit=4629663 read=45769, temp read=67405 written=67438
16. 4,254.157 15,286.528 ↓ 43.2 441,239 1

Sort (cost=146,461.64..146,487.18 rows=10,213 width=485) (actual time=13,418.226..15,286.528 rows=441,239 loops=1)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t2.date_time
  • Sort Key: t1.unique_id
  • Sort Method: external merge Disk: 197888kB
  • Buffers: shared hit=4629663 read=45769, temp read=67405 written=67438
17. 1,040.779 11,032.371 ↓ 43.2 441,239 1

Gather (cost=90,076.09..143,545.05 rows=10,213 width=485) (actual time=5,630.531..11,032.371 rows=441,239 loops=1)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t2.date_time
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=4629663 read=45769, temp read=14074 written=14032
18. 3,534.551 9,991.592 ↓ 34.6 147,080 3

Nested Loop (cost=89,076.09..141,523.75 rows=4,255 width=485) (actual time=5,617.993..9,991.592 rows=147,080 loops=3)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes, t2.unique_id, t2.date_time
  • Buffers: shared hit=4629663 read=45769, temp read=14074 written=14032
  • Worker 0: actual time=5612.077..9932.751 rows=139790 loops=1
  • Buffers: shared hit=1518919 read=15396, temp read=4703 written=4689
  • Worker 1: actual time=5611.838..9969.102 rows=159532 loops=1
  • Buffers: shared hit=1579183 read=16513, temp read=4907 written=4893
19. 563.000 6,457.006 ↓ 40.6 96,197 3

Hash Join (cost=89,075.67..123,061.12 rows=2,367 width=86) (actual time=5,615.134..6,457.006 rows=96,197 loops=3)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t_allo_offers.unique_id
  • Inner Unique: true
  • Hash Cond: ((t1.unique_id = t_allo_offers.unique_id) AND (t1.date_time = (max(t_allo_offers.date_time))))
  • Buffers: shared hit=3350181 read=16220, temp read=14074 written=14032
  • Worker 0: actual time=5611.111..6398.380 rows=90579 loops=1
  • Buffers: shared hit=1116671 read=5500, temp read=4703 written=4689
  • Worker 1: actual time=5606.366..6503.074 rows=104358 loops=1
  • Buffers: shared hit=1115654 read=6708, temp read=4907 written=4893
20. 281.082 281.082 ↑ 1.2 378,673 3

Parallel Seq Scan on public.t_allo_offers t1 (cost=0.00..17,022.41 rows=473,341 width=90) (actual time=0.510..281.082 rows=378,673 loops=3)

  • Output: t1.price, t1.old_price, t1.actions, t1.rating, t1.reviews_count, t1.unique_id, t1.date_time
  • Buffers: shared hit=11463 read=826
  • Worker 0: actual time=0.415..257.992 rows=381433 loops=1
  • Buffers: shared hit=3848 read=279
  • Worker 1: actual time=0.235..324.999 rows=391018 loops=1
  • Buffers: shared hit=4050 read=268
21. 295.163 5,612.924 ↓ 2.3 288,591 3

Hash (cost=86,593.33..86,593.33 rows=124,823 width=12) (actual time=5,612.923..5,612.924 rows=288,591 loops=3)

  • Output: t_allo_offers.unique_id, (max(t_allo_offers.date_time))
  • Buckets: 131072 (originally 131072) Batches: 8 (originally 2) Memory Usage: 3073kB
  • Buffers: shared hit=3338687 read=15387, temp written=3312
  • Worker 0: actual time=5608.617..5608.617 rows=288591 loops=1
  • Buffers: shared hit=1112811 read=5214, temp written=1104
  • Worker 1: actual time=5603.312..5603.313 rows=288591 loops=1
  • Buffers: shared hit=1111585 read=6440, temp written=1104
22. 1,195.924 5,317.761 ↓ 2.3 288,591 3

GroupAggregate (cost=0.43..85,345.10 rows=124,823 width=12) (actual time=0.884..5,317.761 rows=288,591 loops=3)

  • Output: t_allo_offers.unique_id, max(t_allo_offers.date_time)
  • Group Key: t_allo_offers.unique_id
  • Buffers: shared hit=3338687 read=15387
  • Worker 0: actual time=0.502..5338.907 rows=288591 loops=1
  • Buffers: shared hit=1112811 read=5214
  • Worker 1: actual time=0.249..5315.879 rows=288591 loops=1
  • Buffers: shared hit=1111585 read=6440
23. 4,121.837 4,121.837 ↑ 1.0 1,136,018 3

Index Scan using t_allo_offers_uid_index on public.t_allo_offers (cost=0.43..78,416.78 rows=1,136,018 width=12) (actual time=0.248..4,121.837 rows=1,136,018 loops=3)

  • Output: t_allo_offers.unique_id, t_allo_offers.date_time, t_allo_offers.price, t_allo_offers.old_price, t_allo_offers.actions, t_allo_offers.reviews_count, t_allo_offers.rating
  • Buffers: shared hit=3338687 read=15387
  • Worker 0: actual time=0.496..4145.837 rows=1136018 loops=1
  • Buffers: shared hit=1112811 read=5214
  • Worker 1: actual time=0.235..4072.195 rows=1136018 loops=1
  • Buffers: shared hit=1111585 read=6440
24. 0.035 0.035 ↑ 1.0 2 288,591

Index Scan using t_allo_description_uid_index on public.t_allo_description t2 (cost=0.42..7.78 rows=2 width=399) (actual time=0.024..0.035 rows=2 loops=288,591)

  • Output: t2.unique_id, t2.date_time, t2.name, t2.delivery_method, t2.status, t2.url, t2.attributes
  • Index Cond: (t2.unique_id = t1.unique_id)
  • Buffers: shared hit=1279482 read=29549
  • Worker 0: actual time=0.025..0.037 rows=2 loops=90579
  • Buffers: shared hit=402248 read=9896
  • Worker 1: actual time=0.022..0.031 rows=2 loops=104358
  • Buffers: shared hit=463529 read=9805
25. 1,649.790 1,649.790 ↑ 1.0 1 183,310

Index Scan using t_allo_ids_pkey on public.t_allo_ids (cost=0.42..5.33 rows=1 width=145) (actual time=0.009..0.009 rows=1 loops=183,310)

  • Output: t_allo_ids.unique_id, t_allo_ids.site_id, t_allo_ids.cat_1, t_allo_ids.cat_2, t_allo_ids.cat_3, t_allo_ids.cat_4
  • Index Cond: (t_allo_ids.unique_id = t1.unique_id)
  • Buffers: shared hit=726557 read=7174