explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D56q

Settings
# exclusive inclusive rows x rows loops node
1. 0.220 848.918 ↑ 74.5 32 1

Nested Loop Left Join (cost=0.85..5,380,784,078.33 rows=2,385 width=1,113) (actual time=199.179..848.918 rows=32 loops=1)

  • Join Filter: (h_entity.factset_entity_id = ce_events_coverage.factset_entity_id)
2. 0.080 227.162 ↑ 74.5 32 1

Nested Loop Left Join (cost=0.42..78,000.32 rows=2,385 width=1,562) (actual time=178.173..227.162 rows=32 loops=1)

  • Join Filter: (ce_market_time_map.market_time_code = ce_events.market_time)
  • Rows Removed by Join Filter: 128
3. 0.132 227.018 ↑ 74.5 32 1

Nested Loop Left Join (cost=0.42..77,820.39 rows=2,385 width=1,512) (actual time=178.158..227.018 rows=32 loops=1)

  • Join Filter: ((ce_event_type_map.event_type_code)::text = (ce_events.event_type)::text)
  • Rows Removed by Join Filter: 256
4. 0.236 226.790 ↑ 74.5 32 1

Nested Loop (cost=0.42..77,497.30 rows=2,385 width=1,008) (actual time=178.125..226.790 rows=32 loops=1)

5. 224.834 224.834 ↑ 20.8 344 1

Seq Scan on ce_events_coverage (cost=0.00..29,810.00 rows=7,154 width=40) (actual time=0.189..224.834 rows=344 loops=1)

  • Filter: (factset_entity_id = '000BN8-E'::bpchar)
  • Rows Removed by Filter: 2205366
6. 1.720 1.720 ↓ 0.0 0 344

Index Scan using ce_events_pkey on ce_events (cost=0.42..6.66 rows=1 width=968) (actual time=0.005..0.005 rows=0 loops=344)

  • Index Cond: (event_id = ce_events_coverage.event_id)
  • Filter: (event_datetime_utc >= '2018-03-11 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
7. 0.088 0.096 ↑ 1.0 9 32

Materialize (cost=0.00..1.14 rows=9 width=528) (actual time=0.001..0.003 rows=9 loops=32)

8. 0.008 0.008 ↑ 1.0 9 1

Seq Scan on ce_event_type_map (cost=0.00..1.09 rows=9 width=528) (actual time=0.004..0.008 rows=9 loops=1)

9. 0.060 0.064 ↑ 1.0 5 32

Materialize (cost=0.00..1.07 rows=5 width=66) (actual time=0.000..0.002 rows=5 loops=32)

10. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on ce_market_time_map (cost=0.00..1.05 rows=5 width=66) (actual time=0.004..0.004 rows=5 loops=1)

11. 0.013 0.032 ↑ 1.0 1 32

Materialize (cost=0.43..8.45 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=32)

12. 0.019 0.019 ↑ 1.0 1 1

Index Scan using h_entity_pkey on h_entity (cost=0.43..8.45 rows=1 width=37) (actual time=0.017..0.019 rows=1 loops=1)

  • Index Cond: (factset_entity_id = '000BN8-E'::bpchar)
13.          

SubPlan (forNested Loop Left Join)

14. 0.768 1.280 ↑ 1.0 1 32

Aggregate (cost=58,734.94..58,734.95 rows=1 width=32) (actual time=0.040..0.040 rows=1 loops=32)

15. 0.096 0.256 ↑ 55.0 1 32

Bitmap Heap Scan on ce_reports (cost=4.99..218.82 rows=55 width=5,184) (actual time=0.007..0.008 rows=1 loops=32)

  • Recheck Cond: ((event_id = ce_events_coverage.event_id) AND (factset_entity_id = ce_events_coverage.factset_entity_id))
  • Heap Blocks: exact=32
16. 0.160 0.160 ↑ 55.0 1 32

Bitmap Index Scan on ce_reports_event_id_factset_entity_id_idx (cost=0.00..4.98 rows=55 width=0) (actual time=0.005..0.005 rows=1 loops=32)

  • Index Cond: ((event_id = ce_events_coverage.event_id) AND (factset_entity_id = ce_events_coverage.factset_entity_id))
17.          

SubPlan (forAggregate)

18. 0.096 0.256 ↑ 1.0 1 32

Aggregate (cost=1,063.86..1,063.87 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=32)

19. 0.032 0.160 ↓ 0.0 0 32

Bitmap Heap Scan on ce_report_slides (cost=19.01..1,063.03 rows=334 width=516) (actual time=0.005..0.005 rows=0 loops=32)

  • Recheck Cond: (report_id = ce_reports.report_id)
  • Heap Blocks: exact=6
20. 0.128 0.128 ↓ 0.0 0 32

Bitmap Index Scan on ce_report_slides_pkey (cost=0.00..18.92 rows=334 width=0) (actual time=0.004..0.004 rows=0 loops=32)

  • Index Cond: (report_id = ce_reports.report_id)
21. 4.928 620.224 ↑ 1.0 1 32

Aggregate (cost=2,197,326.26..2,197,326.27 rows=1 width=32) (actual time=19.382..19.382 rows=1 loops=32)

22. 1.568 604.640 ↑ 3.7 37 32

Nested Loop (cost=0.99..11,899.55 rows=136 width=21) (actual time=11.124..18.895 rows=37 loops=32)

23. 0.736 593.888 ↑ 9.2 41 32

Nested Loop (cost=0.56..11,689.30 rows=379 width=9) (actual time=11.115..18.559 rows=41 loops=32)

24. 590.976 590.976 ↑ 242.5 2 32

Seq Scan on ce_sec_entity (cost=0.00..2,410.98 rows=485 width=36) (actual time=0.669..18.468 rows=2 loops=32)

  • Filter: (factset_entity_id = ce_events_coverage.factset_entity_id)
  • Rows Removed by Filter: 188001
25. 2.176 2.176 ↓ 20.0 20 64

Index Scan using sym_coverage_fsym_security_id_idx on sym_coverage (cost=0.56..19.12 rows=1 width=18) (actual time=0.016..0.034 rows=20 loops=64)

  • Index Cond: (fsym_security_id = ce_sec_entity.fsym_id)
  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 10
26. 9.184 9.184 ↑ 1.0 1 1,312

Index Scan using sym_ticker_exchange_pkey on sym_ticker_exchange (cost=0.43..0.54 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=1,312)

  • Index Cond: (fsym_id = sym_coverage.fsym_id)
27.          

SubPlan (forAggregate)

28. 1.600 10.656 ↓ 0.0 0 1,184

Nested Loop (cost=1.12..16,069.30 rows=524 width=9) (actual time=0.009..0.009 rows=0 loops=1,184)

29. 8.288 8.288 ↓ 0.0 0 1,184

Index Scan using sym_coverage_fsym_primary_listing_id_idx on sym_coverage sym_cov1 (cost=0.56..1,975.61 rows=525 width=9) (actual time=0.007..0.007 rows=0 loops=1,184)

  • Index Cond: (fsym_primary_listing_id = sym_ticker_exchange.fsym_id)
  • Filter: (fsym_regional_id IS NOT NULL)
30. 0.768 0.768 ↓ 0.0 0 96

Index Only Scan using sym_coverage_fsym_primary_listing_id_idx on sym_coverage sym_cov2 (cost=0.56..21.60 rows=525 width=9) (actual time=0.008..0.008 rows=0 loops=96)

  • Index Cond: (fsym_primary_listing_id = sym_cov1.fsym_id)
  • Heap Fetches: 0