explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ShsA

Settings
# exclusive inclusive rows x rows loops node
1. 18,985.598 68,633.236 ↑ 4.4 86 1

Gather (cost=392,619.18..774,629.15 rows=377 width=107) (actual time=47,481.399..68,633.236 rows=86 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 0.000 49,647.638 ↑ 5.2 43 2 / 2

Nested Loop (cost=391,619.18..773,591.45 rows=222 width=107) (actual time=47,445.317..49,647.638 rows=43 loops=2)

3. 2,255.067 49,646.545 ↑ 35.1 1,272 2 / 2

Parallel Hash Join (cost=391,618.61..486,657.66 rows=44,678 width=107) (actual time=47,445.070..49,646.545 rows=1,272 loops=2)

  • Hash Cond: (ch.ownedcompanyid = s.companyid)
4. 0.069 0.277 ↑ 9.1 21 2 / 2

Hash Join (cost=11.50..68,330.71 rows=192 width=95) (actual time=0.158..0.277 rows=21 loops=2)

  • Hash Cond: (ch.periodid = hp.periodid)
5. 0.016 0.168 ↑ 9.3 21 2 / 2

Hash Left Join (cost=9.04..68,327.70 rows=195 width=81) (actual time=0.054..0.168 rows=21 loops=2)

  • Hash Cond: (ch.ownerobjectid = co.companyid)
6. 0.138 0.138 ↑ 9.3 21 2 / 2

Parallel Index Scan using pk_ciqowncompanyholding on ciqowncompanyholding ch (cost=0.57..68,316.55 rows=195 width=38) (actual time=0.031..0.138 rows=21 loops=2)

  • Index Cond: (ownerobjectid = 109969990)
  • Filter: (holdingdate = '2020-03-31 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 463
7. 0.002 0.014 ↑ 1.0 1 2 / 2

Hash (cost=8.46..8.46 rows=1 width=47) (actual time=0.014..0.014 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.012 0.012 ↑ 1.0 1 2 / 2

Index Scan using pk_ciqcompany on ciqcompany co (cost=0.44..8.46 rows=1 width=47) (actual time=0.012..0.012 rows=1 loops=2)

  • Index Cond: (companyid = 109969990)
9. 0.011 0.040 ↓ 1.0 66 2 / 2

Hash (cost=1.65..1.65 rows=65 width=18) (actual time=0.040..0.040 rows=66 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.029 0.029 ↓ 1.0 66 2 / 2

Seq Scan on ciqownholdingperiod hp (cost=0.00..1.65 rows=65 width=18) (actual time=0.022..0.029 rows=66 loops=2)

11. 33,881.555 47,391.201 ↓ 1.2 1,620,698 2 / 2

Parallel Hash (cost=368,016.23..368,016.23 rows=1,357,110 width=16) (actual time=47,391.200..47,391.201 rows=1,620,698 loops=2)

  • Buckets: 131072 (originally 131072) Batches: 65536 (originally 64) Memory Usage: 8512kB
12. 11,460.340 13,509.646 ↓ 1.2 1,620,698 2 / 2

Parallel Hash Join (cost=156,036.30..368,016.23 rows=1,357,110 width=16) (actual time=3,107.163..13,509.646 rows=1,620,698 loops=2)

  • Hash Cond: (s.securityid = ti.securityid)
13. 953.950 953.950 ↓ 1.2 5,102,662 2 / 2

Parallel Seq Scan on ciqsecurity s (cost=0.00..150,222.59 rows=4,278,859 width=8) (actual time=0.009..953.950 rows=5,102,662 loops=2)

14. 334.414 1,095.356 ↓ 1.2 1,620,872 2 / 2

Parallel Hash (cost=132,445.42..132,445.42 rows=1,357,110 width=16) (actual time=1,095.356..1,095.356 rows=1,620,872 loops=2)

  • Buckets: 131072 Batches: 64 Memory Usage: 3168kB
15. 760.942 760.942 ↓ 1.2 1,620,872 2 / 2

Parallel Seq Scan on ciqtradingitem ti (cost=0.00..132,445.42 rows=1,357,110 width=16) (actual time=0.008..760.942 rows=1,620,872 loops=2)

  • Filter: (currencyid = 160)
  • Rows Removed by Filter: 3834848
16. 2.544 2.544 ↓ 0.0 0 2,544 / 2

Index Only Scan using pk_ciqpriceequity on ciqpriceequity pe (cost=0.57..12.27 rows=2 width=12) (actual time=0.002..0.002 rows=0 loops=2,544)

  • Index Cond: ((tradingitemid = ti.tradingitemid) AND (pricingdate <= ch.holdingdate) AND (pricingdate = '2020-03-31 00:00:00'::timestamp without time zone))
  • Heap Fetches: 86
Planning time : 2.892 ms
Execution time : 68,637.457 ms