explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L1D

Settings
# exclusive inclusive rows x rows loops node
1. 765.294 9,384.674 ↓ 719,492.0 719,492 1

Merge Join (cost=338,570.98..338,694.85 rows=1 width=144) (actual time=8,398.469..9,384.674 rows=719,492 loops=1)

  • Merge Cond: ((t_production.state_fips = t_planted.state_fips) AND (t_production.county_fips = t_planted.county_fips) AND (t_production.district_code = t_planted.district_code) AND (t_production.agg_level = t_planted.agg_level) AND (t_production.commodity = t_planted.commodity) AND (t_production.year = t_planted.year))
2.          

CTE t

3. 593.203 1,356.280 ↑ 2.5 1,662,507 1

Nested Loop (cost=0.00..129,828.32 rows=4,163,200 width=126) (actual time=0.040..1,356.280 rows=1,662,507 loops=1)

4. 13.701 13.701 ↑ 1.0 41,632 1

Seq Scan on yields (cost=0.00..4,932.32 rows=41,632 width=838) (actual time=0.006..13.701 rows=41,632 loops=1)

5. 749.376 749.376 ↑ 2.5 40 41,632

Function Scan on jsonb_each_text e (cost=0.00..1.00 rows=100 width=64) (actual time=0.016..0.018 rows=40 loops=41,632)

6.          

CTE t_production

7. 3,096.870 3,096.870 ↓ 2,236.9 930,533 1

CTE Scan on t (cost=0.00..104,080.00 rows=416 width=144) (actual time=45.883..3,096.870 rows=930,533 loops=1)

  • Filter: (lower(description) ~~ '%production%'::text)
  • Rows Removed by Filter: 731974
8.          

CTE t_planted

9. 1,131.772 1,131.772 ↓ 109.9 731,974 1

CTE Scan on t t_1 (cost=0.00..104,080.00 rows=6,661 width=144) (actual time=0.045..1,131.772 rows=731,974 loops=1)

  • Filter: (lower(description) ~~ '%acres%'::text)
  • Rows Removed by Filter: 930533
10. 1,751.296 5,174.861 ↓ 2,236.9 930,533 1

Sort (cost=26.42..27.46 rows=416 width=112) (actual time=5,034.045..5,174.861 rows=930,533 loops=1)

  • Sort Key: t_production.state_fips, t_production.county_fips, t_production.district_code, t_production.agg_level, t_production.commodity, t_production.year
  • Sort Method: external merge Disk: 47448kB
11. 3,423.565 3,423.565 ↓ 2,236.9 930,533 1

CTE Scan on t_production (cost=0.00..8.32 rows=416 width=112) (actual time=45.885..3,423.565 rows=930,533 loops=1)

12. 2,098.500 3,444.519 ↓ 109.9 731,974 1

Sort (cost=556.24..572.90 rows=6,661 width=112) (actual time=3,364.376..3,444.519 rows=731,974 loops=1)

  • Sort Key: t_planted.state_fips, t_planted.county_fips, t_planted.district_code, t_planted.agg_level, t_planted.commodity, t_planted.year
  • Sort Method: external sort Disk: 41456kB
13. 1,346.019 1,346.019 ↓ 109.9 731,974 1

CTE Scan on t_planted (cost=0.00..133.22 rows=6,661 width=112) (actual time=0.047..1,346.019 rows=731,974 loops=1)