explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PgGF : ttt1

Settings
# exclusive inclusive rows x rows loops node
1. 156.768 81,051.119 ↓ 4.8 193,085 1

HashAggregate (cost=815,312.19..815,712.19 rows=40,000 width=27) (actual time=81,009.431..81,051.119 rows=193,085 loops=1)

  • Group Key: expected_shipments_ge20200809_lt20210207.gtin, expected_shipments_ge20200809_lt20210207.str_id
2. 61.909 80,894.351 ↑ 1.0 252,393 1

Hash Semi Join (cost=804,583.82..813,349.60 rows=261,678 width=23) (actual time=9,646.755..80,894.351 rows=252,393 loops=1)

  • Hash Cond: ((expected_shipments_ge20200809_lt20210207.str_id)::text = (valid_stores_ge20200809_lt20210808.str_id)::text)
3. 0.000 80,831.699 ↑ 1.7 303,184 1

Nested Loop (cost=804,418.07..808,898.88 rows=523,356 width=23) (actual time=9,644.349..80,831.699 rows=303,184 loops=1)

4. 4,360.307 11,573.170 ↓ 28,941.9 5,788,371 1

HashAggregate (cost=804,417.51..804,419.51 rows=200 width=15) (actual time=9,639.878..11,573.170 rows=5,788,371 loops=1)

  • Group Key: (products_ge20201004_lt20201018.gtin)::text
5. 484.870 7,212.863 ↓ 1.1 5,788,371 1

Result (cost=0.56..790,846.62 rows=5,428,356 width=15) (actual time=0.042..7,212.863 rows=5,788,371 loops=1)

6. 380.601 6,727.993 ↓ 1.1 5,788,371 1

Append (cost=0.56..790,846.62 rows=5,428,356 width=15) (actual time=0.041..6,727.993 rows=5,788,371 loops=1)

7. 6,347.392 6,347.392 ↓ 1.1 5,788,371 1

Index Scan using products_ge20201004_lt20201018_upload_date_idx on products_ge20201004_lt20201018 (cost=0.56..763,704.84 rows=5,428,356 width=15) (actual time=0.040..6,347.392 rows=5,788,371 loops=1)

  • Index Cond: (upload_date = '2020-10-06 00:00:00'::timestamp without time zone)
  • Filter: (((genderage)::text = 'Mens'::text) AND ((division)::text = 'Apparel'::text))
  • Rows Removed by Filter: 6,309,757
8. 5,788.371 69,460.452 ↓ 0.0 0 5,788,371

Append (cost=0.56..22.06 rows=34 width=23) (actual time=0.012..0.012 rows=0 loops=5,788,371)

9. 63,672.081 63,672.081 ↓ 0.0 0 5,788,371

Index Scan using expected_shipments_ge20200809_gtin_str_id_expected_arrival__key on expected_shipments_ge20200809_lt20210207 (cost=0.56..21.89 rows=34 width=23) (actual time=0.011..0.011 rows=0 loops=5,788,371)

  • Index Cond: (((gtin)::text = (products_ge20201004_lt20201018.gtin)::text) AND (expected_arrival_date >= '2020-08-05 00:00:00'::timestamp without time zone) AND (expected_arrival_date < '2020-11-01 00:00:00'::timestamp without time zone) AND (upload_date = '2020-10-06 00:00:00'::timestamp without time zone))
10. 0.023 0.743 ↑ 1.0 189 1

Hash (cost=163.35..163.35 rows=192 width=5) (actual time=0.743..0.743 rows=189 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
11. 0.015 0.720 ↑ 1.0 189 1

Append (cost=0.00..163.35 rows=192 width=5) (actual time=0.648..0.720 rows=189 loops=1)

12. 0.705 0.705 ↑ 1.0 189 1

Seq Scan on valid_stores_ge20200809_lt20210808 (cost=0.00..162.39 rows=192 width=5) (actual time=0.648..0.705 rows=189 loops=1)

  • Filter: (upload_date = '2020-10-06 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 7,208