explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7vv

Settings
# exclusive inclusive rows x rows loops node
1. 240.537 152,542.682 ↑ 4.2 1,636,935 1

Hash Join (cost=900,344.48..2,857,172.83 rows=6,796,539 width=34) (actual time=3,917.699..152,542.682 rows=1,636,935 loops=1)

  • Hash Cond: (a.security_id = c.id)
2. 2,433.741 152,290.368 ↑ 4.2 1,636,935 1

Hash Join (cost=889,543.65..2,828,528.01 rows=6,796,539 width=42) (actual time=3,905.890..152,290.368 rows=1,636,935 loops=1)

  • Hash Cond: (b.quote_id = a.id)
3. 145,970.140 149,825.212 ↑ 1.0 34,150,178 1

Bitmap Heap Scan on sp_ts_price b (cost=881,186.71..2,624,119.73 rows=34,156,252 width=34) (actual time=3,865.038..149,825.212 rows=34,150,178 loops=1)

  • Recheck Cond: ((price_date >= '2012-05-13 00:00:00'::timestamp without time zone) AND (price_date <= '2020-01-23 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 2670622
  • Heap Blocks: exact=49247 lossy=430753
4. 3,855.072 3,855.072 ↑ 1.0 34,150,178 1

Bitmap Index Scan on sp_ts_price_quote_price (cost=0.00..872,647.64 rows=34,156,252 width=0) (actual time=3,855.072..3,855.072 rows=34,150,178 loops=1)

  • Index Cond: ((price_date >= '2012-05-13 00:00:00'::timestamp without time zone) AND (price_date <= '2020-01-23 00:00:00'::timestamp without time zone))
5. 0.185 31.415 ↑ 4.4 1,101 1

Hash (cost=8,296.01..8,296.01 rows=4,875 width=20) (actual time=31.414..31.415 rows=1,101 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 125kB
6. 1.750 31.230 ↑ 4.4 1,101 1

Hash Join (cost=3,227.90..8,296.01 rows=4,875 width=20) (actual time=25.809..31.230 rows=1,101 loops=1)

  • Hash Cond: (a.security_id = its_universe_component.m_security_id)
7. 4.217 4.217 ↑ 1.0 24,505 1

Index Scan using i_quote_pkey on i_quote a (cost=0.29..5,004.06 rows=24,505 width=12) (actual time=0.009..4.217 rows=24,505 loops=1)

  • Filter: (primary_exchange = 1)
8. 0.151 25.263 ↑ 4.4 1,101 1

Hash (cost=3,166.67..3,166.67 rows=4,875 width=8) (actual time=25.263..25.263 rows=1,101 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 108kB
9. 12.951 25.112 ↑ 4.4 1,101 1

HashAggregate (cost=3,069.17..3,117.92 rows=4,875 width=8) (actual time=24.980..25.112 rows=1,101 loops=1)

  • Group Key: its_universe_component.m_security_id
10. 12.161 12.161 ↑ 1.0 95,577 1

Index Scan using its_universe_component_m_universe_id on its_universe_component (cost=0.29..2,830.19 rows=95,594 width=8) (actual time=0.009..12.161 rows=95,577 loops=1)

  • Index Cond: (m_universe_id = 106)
11. 2.964 11.777 ↑ 1.0 24,505 1

Hash (cost=10,494.52..10,494.52 rows=24,505 width=8) (actual time=11.776..11.777 rows=24,505 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1214kB
12. 8.813 8.813 ↑ 1.0 24,505 1

Index Only Scan using m_security_pkey on m_security c (cost=0.29..10,494.52 rows=24,505 width=8) (actual time=0.007..8.813 rows=24,505 loops=1)

  • Heap Fetches: 24505