explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AyCE

Settings
# exclusive inclusive rows x rows loops node
1. 34.414 2,329.589 ↑ 9.0 24 1

GroupAggregate (cost=5,920,344.00..5,920,542.46 rows=216 width=92) (actual time=2,276.739..2,329.589 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.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 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. 122.168 2,295.174 ↓ 14.7 214,798 1

Sort (cost=5,920,343.97..5,920,380.53 rows=14,624 width=48) (actual time=2,272.237..2,295.174 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. 163.239 2,173.006 ↓ 14.7 214,798 1

Nested Loop Left Join (cost=236.28..5,919,332.27 rows=14,624 width=48) (actual time=0.246..2,173.006 rows=214,798 loops=1)

8. 25.676 1,580.171 ↓ 14.7 214,798 1

Nested Loop (cost=235.71..5,840,489.25 rows=14,624 width=40) (actual time=0.235..1,580.171 rows=214,798 loops=1)

9. 0.063 0.063 ↑ 1.0 138 1

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

10. 1,476.462 1,554.432 ↓ 14.7 1,557 138

Bitmap Heap Scan on instntradesummary x0 (cost=235.71..42,321.31 rows=106 width=44) (actual time=0.692..11.264 rows=1,557 loops=138)

  • Recheck Cond: (keyinstn = sud.keyinstn)
  • Filter: ((updoperation < 2) AND ((((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. 77.970 77.970 ↑ 1.0 12,460 138

Bitmap Index Scan on ix_instntradesummary_sk (cost=0.00..235.68 rows=12,747 width=0) (actual time=0.565..0.565 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)