explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yMvb

Settings
# exclusive inclusive rows x rows loops node
1. 22.339 1,741.788 ↑ 1,327.8 5 1

GroupAggregate (cost=5,765,548.70..5,765,731.27 rows=6,639 width=59) (actual time=1,709.800..1,741.788 rows=5 loops=1)

  • Group Key: x0.panjivacountrytrading, tc.cik
2. 108.933 1,719.449 ↓ 17.9 118,779 1

Sort (cost=5,765,548.70..5,765,565.29 rows=6,639 width=47) (actual time=1,707.030..1,719.449 rows=118,779 loops=1)

  • Sort Key: x0.panjivacountrytrading, tc.cik
  • Sort Method: external merge Disk: 5640kB
3. 217.413 1,610.516 ↓ 17.9 118,779 1

Nested Loop (cost=345.21..5,765,127.23 rows=6,639 width=47) (actual time=0.138..1,610.516 rows=118,779 loops=1)

  • Join Filter: ((x0.panjivacountrytrading)::text = (tc.micountry)::text)
  • Rows Removed by Join Filter: 1250701
4. 29.177 1,393.103 ↓ 18.9 273,896 1

Nested Loop (cost=345.21..5,745,522.20 rows=14,514 width=39) (actual time=0.132..1,393.103 rows=273,896 loops=1)

5. 0.102 0.102 ↓ 2.3 328 1

Seq Scan on industrycompanies ic (cost=0.00..11.40 rows=140 width=4) (actual time=0.006..0.102 rows=328 loops=1)

6. 1,275.592 1,363.824 ↓ 8.0 835 328

Bitmap Heap Scan on instntradesummary x0 (cost=345.21..41,038.32 rows=104 width=47) (actual time=0.352..4.158 rows=835 loops=328)

  • Recheck Cond: ((keyinstn = ic.keyinstn) AND (updoperation < 2))
  • Filter: ((((tradedatayear)::double precision > date_part('year'::text, (CURRENT_DATE + '-2 years'::interval))) AND ((tradedatayear)::double precision < date_part('year'::text, (CURRENT_DATE)::timestamp without time zone))) OR (((tradedatayear)::double precision = date_part('year'::text, (CURRENT_DATE + '-2 years'::interval))) AND ((tradedatamonth)::double precision >= date_part('month'::text, (CURRENT_DATE)::timestamp without time zone))) OR (((tradedatayear)::double precision = date_part('year'::text, (CURRENT_DATE)::timestamp without time zone)) AND ((tradedatamonth)::double precision <= date_part('month'::text, (CURRENT_DATE)::timestamp without time zone))))
  • Rows Removed by Filter: 4004
  • Heap Blocks: exact=201977
7. 88.232 88.232 ↑ 2.5 5,084 328

Bitmap Index Scan on ai_instntradesummary_24233 (cost=0.00..345.18 rows=12,470 width=0) (actual time=0.269..0.269 rows=5,084 loops=328)

  • Index Cond: (keyinstn = ic.keyinstn)
8. 0.000 0.000 ↑ 18.0 5 273,896

Materialize (cost=0.00..11.35 rows=90 width=20) (actual time=0.000..0.000 rows=5 loops=273,896)

9. 0.004 0.004 ↑ 18.0 5 1

Seq Scan on topcountries tc (cost=0.00..10.90 rows=90 width=20) (actual time=0.003..0.004 rows=5 loops=1)

Planning time : 0.998 ms