explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r97G

Settings
# exclusive inclusive rows x rows loops node
1. 22.316 1,723.500 ↑ 1,327.8 5 1

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

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

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

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

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

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

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

5. 0.101 0.101 ↓ 2.3 328 1

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

6. 1,259.848 1,347.752 ↓ 8.0 835 328

Bitmap Heap Scan on instntradesummary x0 (cost=345.21..41,038.32 rows=104 width=47) (actual time=0.351..4.109 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. 87.904 87.904 ↑ 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.268..0.268 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.426 ms