explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hnu

Settings
# exclusive inclusive rows x rows loops node
1. 34.393 2,109.127 ↑ 9.0 24 1

GroupAggregate (cost=5,926,952.17..5,927,150.63 rows=216 width=92) (actual time=2,056.369..2,109.127 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.011 0.025 ↑ 1.0 1 1

Aggregate (cost=2.73..2.73 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=1)

4. 0.014 0.014 ↑ 1.0 138 1

Seq Scan on industrycompanies (cost=0.00..2.38 rows=138 width=4) (actual time=0.007..0.014 rows=138 loops=1)

5. 0.010 0.020 ↑ 1.0 1 1

Aggregate (cost=2.73..2.73 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

6. 0.010 0.010 ↑ 1.0 138 1

Seq Scan on industrycompanies industrycompanies_1 (cost=0.00..2.38 rows=138 width=4) (actual time=0.003..0.010 rows=138 loops=1)

7. 0.011 0.021 ↑ 1.0 1 1

Aggregate (cost=2.73..2.73 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

8. 0.010 0.010 ↑ 1.0 138 1

Seq Scan on industrycompanies industrycompanies_2 (cost=0.00..2.38 rows=138 width=4) (actual time=0.002..0.010 rows=138 loops=1)

9. 110.272 2,074.668 ↓ 14.7 214,798 1

Sort (cost=5,926,943.97..5,926,980.53 rows=14,624 width=48) (actual time=2,051.770..2,074.668 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
10. 102.796 1,964.396 ↓ 14.7 214,798 1

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

11. 23.382 1,432.004 ↓ 14.7 214,798 1

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

12. 0.056 0.056 ↑ 1.0 138 1

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

13. 1,327.836 1,408.566 ↓ 14.7 1,557 138

Bitmap Heap Scan on instntradesummary x0 (cost=283.53..42,369.13 rows=106 width=44) (actual time=0.710..10.207 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
14. 80.730 80.730 ↑ 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.585..0.585 rows=12,460 loops=138)

  • Index Cond: (keyinstn = sud.keyinstn)
15. 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)