explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ed8A

Settings
# exclusive inclusive rows x rows loops node
1. 34.356 7,331.160 ↑ 9.0 24 1

GroupAggregate (cost=5,926,944.00..5,927,142.46 rows=216 width=92) (actual time=7,278.440..7,331.160 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.001 0.001 ↑ 1.0 1 1

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

5. 0.001 0.001 ↑ 1.0 1 1

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

6. 123.256 7,296.801 ↓ 14.7 214,798 1

Sort (cost=5,926,943.97..5,926,980.53 rows=14,624 width=48) (actual time=7,273.855..7,296.801 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: 8,344kB
7. 189.745 7,173.545 ↓ 14.7 214,798 1

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

8. 26.359 5,050.618 ↓ 14.7 214,798 1

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

9. 0.093 0.093 ↑ 1.0 138 1

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

10. 4,670.886 5,024.166 ↓ 14.7 1,557 138

Bitmap Heap Scan on instntradesummary x0 (cost=283.53..42,369.13 rows=106 width=44) (actual time=2.850..36.407 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: 10,903
  • Heap Blocks: exact=132,923
11. 353.280 353.280 ↑ 1.0 12,460 138

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

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

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

  • Index Cond: (x0.keyinstntradesummary = keyinstntradesummary)