explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xkGr : Optimization for: NNDI-1009; plan #NQ3p

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6.564 32,745.657 ↓ 29.8 745 1

GroupAggregate (cost=292,698.23..292,705.30 rows=25 width=73) (actual time=32,737.532..32,745.657 rows=745 loops=1)

  • Group Key: rlu1.lookupfull, (speciesdisplaynamefromcode(r.species)), loc.abbrev
2. 70.368 32,739.093 ↓ 972.0 24,301 1

Sort (cost=292,698.23..292,698.30 rows=25 width=53) (actual time=32,737.517..32,739.093 rows=24,301 loops=1)

  • Sort Key: rlu1.lookupfull, (speciesdisplaynamefromcode(r.species)), loc.abbrev
  • Sort Method: quicksort Memory: 3,224kB
3. 3,714.690 32,668.725 ↓ 972.0 24,301 1

Nested Loop Left Join (cost=1.30..292,697.65 rows=25 width=53) (actual time=1.076..32,668.725 rows=24,301 loops=1)

  • Join Filter: ((loc.locationcode)::text = "substring"((r.location)::text, 1, 1))
  • Rows Removed by Join Filter: 21,700,793
4. 7.363 27,666.082 ↓ 972.0 24,301 1

Nested Loop Left Join (cost=1.30..292,094.10 rows=25 width=30) (actual time=0.119..27,666.082 rows=24,301 loops=1)

5. 19,734.125 27,585.816 ↓ 972.0 24,301 1

Nested Loop Left Join (cost=0.86..291,896.97 rows=25 width=30) (actual time=0.108..27,585.816 rows=24,301 loops=1)

  • Join Filter: ((rlu1.id)::numeric = res1.resultvalue)
  • Rows Removed by Join Filter: 129,086,912
6. 28.596 172.575 ↓ 972.0 24,301 1

Nested Loop (cost=0.86..289,436.12 rows=25 width=21) (actual time=0.059..172.575 rows=24,301 loops=1)

7. 19.254 19.254 ↑ 1.1 24,945 1

Index Scan using reports_idx_projectid on reports r (cost=0.43..8,877.84 rows=26,694 width=17) (actual time=0.031..19.254 rows=24,945 loops=1)

  • Index Cond: (projectid = 30)
  • Filter: (NOT del)
  • Rows Removed by Filter: 7,995
8. 124.725 124.725 ↑ 1.0 1 24,945

Index Scan using results_idx_reportid on results res1 (cost=0.44..10.50 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=24,945)

  • Index Cond: (reportid = r.id)
  • Filter: ((NOT del) AND (resulttypeid = 9) AND (resultvalue = ANY ('{25,26,27,29,30,31,32,33,34,36,37,38,40,41,42,43,44,45,46,47,48,49,51,52,55,58,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,95,96,97,98,99,100,101,102,103,104,106,107,108,109,110,111,112,113,115,1358,1360,1361,1362,1363,1364,1365,1366,1368,1369,3057,3058,3059,3060,3061,3062,3066,5200,5201,5202,5205}'::numeric[])))
  • Rows Removed by Filter: 4
9. 7,678.025 7,679.116 ↑ 1.0 5,313 24,301

Materialize (cost=0.00..149.69 rows=5,313 width=17) (actual time=0.000..0.316 rows=5,313 loops=24,301)

10. 1.091 1.091 ↑ 1.0 5,313 1

Seq Scan on resultlookup rlu1 (cost=0.00..123.13 rows=5,313 width=17) (actual time=0.011..1.091 rows=5,313 loops=1)

11. 72.903 72.903 ↑ 1.0 1 24,301

Index Scan using results_idx_reportid on results res2 (cost=0.44..7.88 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=24,301)

  • Index Cond: (reportid = r.id)
  • Filter: ((NOT del) AND (resulttypeid = 50))
  • Rows Removed by Filter: 4
12. 1,287.290 1,287.953 ↑ 1.0 894 24,301

Materialize (cost=0.00..208.41 rows=894 width=9) (actual time=0.000..0.053 rows=894 loops=24,301)

13. 0.663 0.663 ↑ 1.0 894 1

Seq Scan on locations loc (cost=0.00..203.94 rows=894 width=9) (actual time=0.005..0.663 rows=894 loops=1)

Planning time : 8.810 ms
Execution time : 32,746.941 ms