explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5hUK

Settings
# exclusive inclusive rows x rows loops node
1. 10.533 2,646,617.769 ↑ 1.0 1 1

Aggregate (cost=10,821,605.79..10,821,605.80 rows=1 width=32) (actual time=2,646,617.769..2,646,617.769 rows=1 loops=1)

2. 0.872 2,646,607.236 ↓ 1.1 2,893 1

Hash Full Join (cost=10,821,118.71..10,821,417.42 rows=2,691 width=8) (actual time=2,646,539.667..2,646,607.236 rows=2,893 loops=1)

  • Hash Cond: (((min(map_universities_emb.qb_university_id)) = qb_universities_states.university_id) AND (emb_offers.campus_state = qb_universities_states.estado))
3. 0.572 2,646,480.613 ↓ 2.4 1,157 1

GroupAggregate (cost=10,821,029.43..10,821,287.80 rows=488 width=11) (actual time=2,646,413.847..2,646,480.613 rows=1,157 loops=1)

  • Group Key: emb_offers.emb_university_id, emb_offers.campus_state
4. 0.582 2,646,480.041 ↑ 3.5 1,954 1

Merge Left Join (cost=10,821,029.43..10,821,230.94 rows=6,931 width=11) (actual time=2,646,413.822..2,646,480.041 rows=1,954 loops=1)

  • Merge Cond: (emb_offers.emb_university_id = map_universities_emb.emb_university_id)
5. 0.000 2,646,465.813 ↑ 4.2 1,157 1

Unique (cost=10,820,952.87..10,820,989.44 rows=4,877 width=7) (actual time=2,646,400.343..2,646,465.813 rows=1,157 loops=1)

6.          

Initplan (for Unique)

7. 0.000 1,107,836.672 ↑ 1.0 1 1

Finalize Aggregate (cost=3,376,726.19..3,376,726.20 rows=1 width=4) (actual time=1,107,836.672..1,107,836.672 rows=1 loops=1)

8. 89.614 1,107,836.726 ↓ 1.5 3 1

Gather (cost=3,376,725.98..3,376,726.19 rows=2 width=4) (actual time=1,107,836.403..1,107,836.726 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 5,869.411 1,107,747.112 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=3,375,725.98..3,375,725.99 rows=1 width=4) (actual time=1,107,747.111..1,107,747.112 rows=1 loops=3)

10. 1,101,877.701 1,101,877.701 ↑ 1.3 20,804,425 3 / 3

Parallel Seq Scan on emb_offers emb_offers_2 (cost=0.00..3,245,698.32 rows=26,005,532 width=8) (actual time=31.572..1,101,877.701 rows=20,804,425 loops=3)

11. 253.818 2,646,444.622 ↓ 41.9 204,308 1

Sort (cost=7,444,226.66..7,444,238.86 rows=4,877 width=7) (actual time=2,646,400.340..2,646,444.622 rows=204,308 loops=1)

  • Sort Key: emb_offers.emb_university_id, emb_offers.campus_state
  • Sort Method: external merge Disk: 3,416kB
12. 90.144 2,646,190.804 ↓ 41.9 204,308 1

Hash Join (cost=3,923,858.44..7,443,927.90 rows=4,877 width=7) (actual time=2,497,327.134..2,646,190.804 rows=204,308 loops=1)

  • Hash Cond: ((emb_offers.campus_state = cities.state) AND (emb_offers.campus_city = emb_cities.campus_city))
13. 1,107,861.919 2,071,219.535 ↑ 1.4 204,308 1

Gather (cost=1,000.00..3,470,345.80 rows=286,060 width=18) (actual time=1,922,445.974..2,071,219.535 rows=204,308 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 0
14. 963,357.616 963,357.616 ↓ 1.7 204,308 1

Parallel Seq Scan on emb_offers (cost=0.00..3,440,739.80 rows=119,192 width=18) (actual time=814,608.248..963,357.616 rows=204,308 loops=1)

  • Filter: ((has_stock > '0'::double precision) AND (date(dump_date) = $1))
  • Rows Removed by Filter: 62,208,968
15. 0.073 574,881.125 ↑ 3.8 179 1

Hash (cost=3,922,848.21..3,922,848.21 rows=682 width=17) (actual time=574,881.125..574,881.125 rows=179 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
16. 1.818 574,881.052 ↑ 3.8 179 1

Merge Join (cost=3,922,710.53..3,922,848.21 rows=682 width=17) (actual time=574,878.870..574,881.052 rows=179 loops=1)

  • Merge Cond: ((emb_cities.campus_state = cities.state) AND ((unaccent(emb_cities.campus_city)) = (unaccent(cities.name))))
17. 0.270 574,809.948 ↑ 27.0 181 1

Sort (cost=3,922,239.32..3,922,251.54 rows=4,887 width=14) (actual time=574,809.923..574,809.948 rows=181 loops=1)

  • Sort Key: emb_cities.campus_state, (unaccent(emb_cities.campus_city))
  • Sort Method: quicksort Memory: 39kB
18. 92.654 574,809.678 ↑ 27.0 181 1

Subquery Scan on emb_cities (cost=3,921,842.14..3,921,939.88 rows=4,887 width=14) (actual time=574,809.555..574,809.678 rows=181 loops=1)

19. 16,465.543 574,717.024 ↑ 27.0 181 1

HashAggregate (cost=3,921,842.14..3,921,891.01 rows=4,887 width=14) (actual time=574,716.969..574,717.024 rows=181 loops=1)

  • Group Key: emb_offers_1.campus_city, emb_offers_1.campus_state
20. 558,251.481 558,251.481 ↑ 1.0 62,413,276 1

Seq Scan on emb_offers emb_offers_1 (cost=0.00..3,609,775.76 rows=62,413,276 width=14) (actual time=0.003..558,251.481 rows=62,413,276 loops=1)

21. 15.587 69.286 ↑ 1.0 5,533 1

Sort (cost=471.20..485.16 rows=5,582 width=16) (actual time=68.922..69.286 rows=5,533 loops=1)

  • Sort Key: cities.state, (unaccent(cities.name))
  • Sort Method: quicksort Memory: 639kB
22. 53.699 53.699 ↑ 1.0 5,582 1

Seq Scan on querobolsa_production_cities cities (cost=0.00..123.82 rows=5,582 width=16) (actual time=21.976..53.699 rows=5,582 loops=1)

  • Filter: (id IS NOT NULL)
23. 0.409 13.646 ↓ 2.2 2,436 1

Sort (cost=76.57..79.32 rows=1,100 width=8) (actual time=13.468..13.646 rows=2,436 loops=1)

  • Sort Key: map_universities_emb.emb_university_id
  • Sort Method: quicksort Memory: 100kB
24. 13.237 13.237 ↑ 1.0 1,100 1

Seq Scan on x9_map_universities_emb map_universities_emb (cost=0.00..21.00 rows=1,100 width=8) (actual time=3.522..13.237 rows=1,100 loops=1)

25. 0.475 125.751 ↑ 1.0 2,691 1

Hash (cost=48.91..48.91 rows=2,691 width=7) (actual time=125.751..125.751 rows=2,691 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 135kB
26. 125.276 125.276 ↑ 1.0 2,691 1

Seq Scan on qb_universities_states (cost=0.00..48.91 rows=2,691 width=7) (actual time=29.555..125.276 rows=2,691 loops=1)

Planning time : 504.564 ms
Execution time : 2,646,667.537 ms