explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QaJR

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 5.889 ↑ 1.0 5 1

Limit (cost=44,514.03..44,514.12 rows=5 width=163) (actual time=5.883..5.889 rows=5 loops=1)

  • Buffers: shared hit=1819
2. 0.011 5.886 ↑ 18.8 5 1

WindowAgg (cost=44,514.03..44,515.68 rows=94 width=163) (actual time=5.882..5.886 rows=5 loops=1)

  • Buffers: shared hit=1819
3. 0.014 5.875 ↑ 18.8 5 1

Sort (cost=44,514.03..44,514.27 rows=94 width=91) (actual time=5.875..5.875 rows=5 loops=1)

  • Sort Key: (sum(COALESCE(x0.shipmentvolumear, x0.shipmentvolumeest, '0'::double precision))) DESC
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=1819
4. 0.143 5.861 ↑ 6.3 15 1

GroupAggregate (cost=44,507.89..44,510.95 rows=94 width=91) (actual time=5.706..5.861 rows=15 loops=1)

  • Group Key: x0.panjivacountrytrading, x0_1.countryname, x0_1.country, x0_2.panjivacompanyname
  • Buffers: shared hit=1819
5. 0.336 5.718 ↓ 5.3 498 1

Sort (cost=44,507.89..44,508.13 rows=94 width=79) (actual time=5.696..5.718 rows=498 loops=1)

  • Sort Key: x0.panjivacountrytrading, x0_1.countryname, x0_1.country, x0_2.panjivacompanyname
  • Sort Method: quicksort Memory: 92kB
  • Buffers: shared hit=1819
6. 0.070 5.382 ↓ 5.3 498 1

Nested Loop (cost=277.67..44,504.81 rows=94 width=79) (actual time=0.420..5.382 rows=498 loops=1)

  • Buffers: shared hit=1819
7. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ai_panjivacompany_27403 on panjivacompany x0_2 (cost=0.43..8.46 rows=1 width=30) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (keyinstn = 4362236)
  • Filter: (updoperation < 2)
  • Buffers: shared hit=4
8. 0.383 5.299 ↓ 5.3 498 1

Nested Loop Left Join (cost=277.24..44,495.42 rows=94 width=57) (actual time=0.405..5.299 rows=498 loops=1)

  • Buffers: shared hit=1815
9. 4.109 4.418 ↓ 5.3 498 1

Bitmap Heap Scan on instntradesummary x0 (cost=277.09..44,463.91 rows=94 width=47) (actual time=0.398..4.418 rows=498 loops=1)

  • Recheck Cond: ((keyinstn = 4362236) AND (panjivacountrytrading IS NOT NULL) 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: 4445
  • Heap Blocks: exact=797
  • Buffers: shared hit=819
10. 0.309 0.309 ↑ 2.3 4,943 1

Bitmap Index Scan on ix_instntradesummary_sk (cost=0.00..277.07 rows=11,250 width=0) (actual time=0.309..0.309 rows=4,943 loops=1)

  • Index Cond: ((keyinstn = 4362236) AND (panjivacountrytrading IS NOT NULL))
  • Buffers: shared hit=22
11. 0.498 0.498 ↑ 1.0 1 498

Index Scan using pk_panjivacountry on panjivacountry x0_1 (cost=0.14..0.34 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=498)

  • Index Cond: ((x0.panjivacountrytrading)::text = (panjivacountry)::text)
  • Filter: (updoperation < 2)
  • Buffers: shared hit=996
Planning time : 0.559 ms