explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BAXr

Settings
# exclusive inclusive rows x rows loops node
1. 34.471 2,115.185 ↑ 9.0 24 1

GroupAggregate (cost=5,926,944.00..5,927,142.46 rows=216 width=92) (actual time=2,062.284..2,115.185 rows=24 loops=1)

  • Group Key: (date_trunc('month'::text, (to_date(((((instntradesummary.tradedatamonth)::character varying)::text || '/1/'::text) || ((instntradesummary.tradedatayear)::character varying)::text), 'mm/d/yyyy'::text))::timestamp with time zone))
2.          

Initplan (for GroupAggregate)

3. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

4. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

5. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

6. 109.418 2,080.714 ↓ 14.7 214,798 1

Sort (cost=5,926,943.97..5,926,980.53 rows=14,624 width=48) (actual time=2,057.797..2,080.714 rows=214,798 loops=1)

  • Sort Key: (date_trunc('month'::text, (to_date(((((instntradesummary.tradedatamonth)::character varying)::text || '/1/'::text) || ((instntradesummary.tradedatayear)::character varying)::text), 'mm/d/yyyy'::text))::timestamp with time zone))
  • Sort Method: external merge Disk: 8344kB
7. 107.397 1,971.296 ↓ 14.7 214,798 1

Nested Loop Left Join (cost=284.10..5,925,932.27 rows=14,624 width=48) (actual time=0.257..1,971.296 rows=214,798 loops=1)

8. 26.927 1,434.303 ↓ 14.7 214,798 1

Nested Loop (cost=283.53..5,847,089.25 rows=14,624 width=40) (actual time=0.245..1,434.303 rows=214,798 loops=1)

9. 0.052 0.052 ↑ 1.0 138 1

Seq Scan on industrycompanies sud (cost=0.00..2.38 rows=138 width=4) (actual time=0.005..0.052 rows=138 loops=1)

10. 1,325.904 1,407.324 ↓ 14.7 1,557 138

Bitmap Heap Scan on instntradesummary x0 (cost=283.53..42,369.13 rows=106 width=44) (actual time=0.716..10.198 rows=1,557 loops=138)

  • Recheck Cond: ((keyinstn = sud.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: 10903
  • Heap Blocks: exact=132923
11. 81.420 81.420 ↑ 1.0 12,460 138

Bitmap Index Scan on ai_instntradesummary_27644 (cost=0.00..283.51 rows=12,747 width=0) (actual time=0.590..0.590 rows=12,460 loops=138)

  • Index Cond: (keyinstn = sud.keyinstn)
12. 429.596 429.596 ↑ 1.0 1 214,798

Index Scan using pk_instntradesummary on instntradesummary (cost=0.57..5.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=214,798)

  • Index Cond: (x0.keyinstntradesummary = keyinstntradesummary)