explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NQ3p : NNDI-1009

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6.250 33,974.295 ↓ 607.0 607 1

GroupAggregate (cost=227,240.55..227,240.84 rows=1 width=73) (actual time=33,966.488..33,974.295 rows=607 loops=1)

  • Group Key: rlu1.lookupfull, (speciesdisplaynamefromcode(r.species)), loc.abbrev
2. 64.835 33,968.045 ↓ 22,272.0 22,272 1

Sort (cost=227,240.55..227,240.56 rows=1 width=53) (actual time=33,966.473..33,968.045 rows=22,272 loops=1)

  • Sort Key: rlu1.lookupfull, (speciesdisplaynamefromcode(r.species)), loc.abbrev
  • Sort Method: quicksort Memory: 3,039kB
3. 3,695.966 33,903.210 ↓ 22,272.0 22,272 1

Nested Loop (cost=1.30..227,240.54 rows=1 width=53) (actual time=0.267..33,903.210 rows=22,272 loops=1)

  • Join Filter: ("substring"((r.location)::text, 1, 1) = (loc.locationcode)::text)
  • Rows Removed by Join Filter: 19,888,896
4. 18,041.097 27,668.236 ↓ 22,272.0 22,272 1

Nested Loop (cost=1.30..227,022.94 rows=1 width=30) (actual time=0.070..27,668.236 rows=22,272 loops=1)

  • Join Filter: (res1.resultvalue = (rlu1.id)::numeric)
  • Rows Removed by Join Filter: 118,308,864
5. 25.297 228.355 ↓ 22,272.0 22,272 1

Nested Loop (cost=1.30..226,820.12 rows=1 width=21) (actual time=0.039..228.355 rows=22,272 loops=1)

  • Join Filter: (r.id = res1.reportid)
6. 22.597 112.434 ↓ 31.3 22,656 1

Nested Loop (cost=0.86..219,364.37 rows=723 width=25) (actual time=0.028..112.434 rows=22,656 loops=1)

7. 15.002 15.002 ↑ 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.011..15.002 rows=24,945 loops=1)

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

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

  • Index Cond: (reportid = r.id)
  • Filter: ((NOT del) AND (resulttypeid = 50))
  • Rows Removed by Filter: 4
9. 90.624 90.624 ↑ 1.0 1 22,656

Index Scan using results_idx_reportid on results res1 (cost=0.44..10.30 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=22,656)

  • Index Cond: (reportid = res2.reportid)
  • 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
10. 9,398.784 9,398.784 ↑ 1.0 5,313 22,272

Seq Scan on resultlookup rlu1 (cost=0.00..123.13 rows=5,313 width=17) (actual time=0.001..0.422 rows=5,313 loops=22,272)

11. 2,539.008 2,539.008 ↑ 1.0 894 22,272

Seq Scan on locations loc (cost=0.00..203.94 rows=894 width=9) (actual time=0.001..0.114 rows=894 loops=22,272)

Planning time : 9.452 ms
Execution time : 33,974.412 ms