explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DkRR

Settings
# exclusive inclusive rows x rows loops node
1. 141.064 108,494.132 ↓ 82,481.4 659,851 1

Subquery Scan on x (cost=2,361,377.01..2,361,411.86 rows=8 width=568) (actual time=107,795.915..108,494.132 rows=659,851 loops=1)

  • Filter: (x.rownum = 1)
  • Rows Removed by Filter: 1,381,633
2. 1,727.815 108,353.068 ↓ 1,317.9 2,041,484 1

HashAggregate (cost=2,361,377.01..2,361,392.50 rows=1,549 width=576) (actual time=107,795.912..108,353.068 rows=2,041,484 loops=1)

  • Group Key: "*SELECT* 1".mnemonic, "*SELECT* 1".companyid, "*SELECT* 1".periodenddate, "*SELECT* 1".filingdate, "*SELECT* 1".dataitemvalue, "*SELECT* 1".rownum
3. 144.638 106,625.253 ↓ 1,317.9 2,041,484 1

Append (cost=1,293,027.12..2,361,353.77 rows=1,549 width=576) (actual time=78,863.703..106,625.253 rows=2,041,484 loops=1)

4. 163.640 81,247.761 ↓ 1,447.4 1,758,586 1

Subquery Scan on *SELECT* 1 (cost=1,293,027.12..1,293,075.72 rows=1,215 width=77) (actual time=78,863.702..81,247.761 rows=1,758,586 loops=1)

5. 1,332.217 81,084.121 ↓ 1,447.4 1,758,586 1

WindowAgg (cost=1,293,027.12..1,293,063.57 rows=1,215 width=87) (actual time=78,863.701..81,084.121 rows=1,758,586 loops=1)

6. 6,055.816 79,751.904 ↓ 1,447.4 1,758,586 1

Sort (cost=1,293,027.12..1,293,030.15 rows=1,215 width=60) (actual time=78,863.681..79,751.904 rows=1,758,586 loops=1)

  • Sort Key: hm.mnemonic, fi.financialperiodid, fi.filingdate DESC, fd.financialcollectionid DESC
  • Sort Method: external merge Disk: 126,416kB
7. 0.000 73,696.088 ↓ 1,447.4 1,758,586 1

Gather (cost=11,121.61..1,292,964.87 rows=1,215 width=60) (actual time=30.996..73,696.088 rows=1,758,586 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 548.274 76,349.553 ↓ 1,158.5 586,195 3 / 3

Nested Loop (cost=10,121.61..1,291,843.37 rows=506 width=60) (actual time=20.783..76,349.553 rows=586,195 loops=3)

9. 77.693 74,628.888 ↓ 1,429.7 586,195 3 / 3

Nested Loop (cost=10,121.18..1,288,616.90 rows=410 width=56) (actual time=20.756..74,628.888 rows=586,195 loops=3)

10. 34.466 3,662.484 ↓ 603.7 69,431 3 / 3

Nested Loop (cost=10,120.47..953,594.59 rows=115 width=36) (actual time=19.345..3,662.484 rows=69,431 loops=3)

11. 17.767 3,269.591 ↓ 578.1 89,607 3 / 3

Nested Loop (cost=10,119.89..952,352.37 rows=155 width=30) (actual time=19.325..3,269.591 rows=89,607 loops=3)

12. 1,066.230 3,146.663 ↓ 505.6 4,045 3 / 3

Hash Join (cost=10,119.32..949,830.49 rows=8 width=30) (actual time=19.293..3,146.663 rows=4,045 loops=3)

  • Hash Cond: (fi.financialperiodid = fp.financialperiodid)
13. 2,074.332 2,074.332 ↑ 1.3 11,208,544 3 / 3

Parallel Seq Scan on ciqfininstance fi (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual time=0.006..2,074.332 rows=11,208,544 loops=3)

  • Filter: ((filingdate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (periodenddate >= '1999-12-31 00:00:00'::timestamp without time zone) AND (periodenddate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (latestfilingforinstanceflag = 1))
  • Rows Removed by Filter: 2,976,017
14. 0.976 6.101 ↓ 461.7 4,617 3 / 3

Hash (cost=10,119.19..10,119.19 rows=10 width=10) (actual time=6.100..6.101 rows=4,617 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 263kB
15. 0.772 5.125 ↓ 461.7 4,617 3 / 3

Nested Loop (cost=1.16..10,119.19 rows=10 width=10) (actual time=0.074..5.125 rows=4,617 loops=3)

16. 0.063 0.253 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..451.21 rows=50 width=8) (actual time=0.040..0.253 rows=50 loops=3)

17. 0.040 0.040 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc (cost=0.28..28.46 rows=50 width=4) (actual time=0.026..0.040 rows=50 loops=3)

  • Index Cond: ((companyid >= 1) AND (companyid <= 24,686))
  • Heap Fetches: 150
18. 0.150 0.150 ↑ 1.0 1 150 / 3

Index Only Scan using pk_ciqcompany on ciqcompany c (cost=0.44..8.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=150)

  • Index Cond: (companyid = hc.companyid)
  • Heap Fetches: 150
19. 4.100 4.100 ↓ 1.8 92 150 / 3

Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp (cost=0.44..192.85 rows=51 width=10) (actual time=0.011..0.082 rows=92 loops=150)

  • Index Cond: ((companyid = c.companyid) AND (periodtypeid = 4))
20. 105.161 105.161 ↑ 3.9 22 12,134 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic (cost=0.57..314.39 rows=85 width=8) (actual time=0.009..0.026 rows=22 loops=12,134)

  • Index Cond: (financialinstanceid = fi.financialinstanceid)
  • Heap Fetches: 268,820
21. 358.427 358.427 ↑ 1.0 1 268,820 / 3

Index Scan using pk_ciqfincollection on ciqfincollection fc (cost=0.57..8.01 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=268,820)

  • Index Cond: (financialcollectionid = ic.financialcollectionid)
  • Filter: (currencyid <> 0)
  • Rows Removed by Filter: 0
22. 0.000 70,888.711 ↑ 41.5 8 208,292 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.665..1.021 rows=8 loops=208,292)

23. 3,749.256 3,749.256 ↑ 1.0 332 208,292 / 3

Seq Scan on hpsmnemonic hm (cost=0.00..8.24 rows=332 width=21) (actual time=0.002..0.054 rows=332 loops=208,292)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
24. 69,152.944 69,152.944 ↓ 0.0 0 69,152,944 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd (cost=0.71..8.73 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=69,152,944)

  • Index Cond: ((financialcollectionid = fc.financialcollectionid) AND (dataitemid = hm.dataitemid))
25. 1,172.391 1,172.391 ↑ 1.0 1 1,758,586 / 3

Index Scan using pk_ciqexchangerate on ciqexchangerate xr (cost=0.43..7.86 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,758,586)

  • Index Cond: ((currencyid = fc.currencyid) AND (pricedate = fi.periodenddate))
  • Filter: (latestsnapflag = 1)
  • Rows Removed by Filter: 0
26. 26.920 25,232.854 ↓ 847.0 282,898 1

Subquery Scan on *SELECT* 2 (cost=1,068,257.79..1,068,270.31 rows=334 width=52) (actual time=24,899.912..25,232.854 rows=282,898 loops=1)

27. 144.876 25,205.934 ↓ 847.0 282,898 1

WindowAgg (cost=1,068,257.79..1,068,266.97 rows=334 width=62) (actual time=24,899.911..25,205.934 rows=282,898 loops=1)

28. 710.761 25,061.058 ↓ 847.0 282,898 1

Sort (cost=1,068,257.79..1,068,258.62 rows=334 width=54) (actual time=24,899.896..25,061.058 rows=282,898 loops=1)

  • Sort Key: hm_1.mnemonic, fi_1.financialperiodid, fi_1.filingdate DESC, fd_1.financialcollectionid DESC
  • Sort Method: external merge Disk: 20,240kB
29. 0.000 24,350.297 ↓ 847.0 282,898 1

Gather (cost=11,120.83..1,068,243.79 rows=334 width=54) (actual time=21.182..24,350.297 rows=282,898 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 26.450 24,654.002 ↓ 678.4 94,299 3 / 3

Nested Loop (cost=10,120.83..1,067,210.39 rows=139 width=54) (actual time=14.399..24,654.002 rows=94,299 loops=3)

31. 88.079 3,442.752 ↓ 517.3 20,176 3 / 3

Nested Loop (cost=10,120.12..953,594.12 rows=39 width=34) (actual time=13.979..3,442.752 rows=20,176 loops=3)

32. 13.683 3,085.853 ↓ 578.1 89,607 3 / 3

Nested Loop (cost=10,119.55..952,351.95 rows=155 width=30) (actual time=13.915..3,085.853 rows=89,607 loops=3)

33. 897.908 2,975.098 ↓ 505.6 4,045 3 / 3

Hash Join (cost=10,118.97..949,830.15 rows=8 width=30) (actual time=13.888..2,975.098 rows=4,045 loops=3)

  • Hash Cond: (fi_1.financialperiodid = fp_1.financialperiodid)
34. 2,072.016 2,072.016 ↑ 1.3 11,208,544 3 / 3

Parallel Seq Scan on ciqfininstance fi_1 (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual time=0.008..2,072.016 rows=11,208,544 loops=3)

  • Filter: ((filingdate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (periodenddate >= '1999-12-31 00:00:00'::timestamp without time zone) AND (periodenddate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (latestfilingforinstanceflag = 1))
  • Rows Removed by Filter: 2,976,017
35. 0.634 5.174 ↓ 461.7 4,617 3 / 3

Hash (cost=10,118.85..10,118.85 rows=10 width=10) (actual time=5.174..5.174 rows=4,617 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 263kB
36. 0.564 4.540 ↓ 461.7 4,617 3 / 3

Nested Loop (cost=1.16..10,118.85 rows=10 width=10) (actual time=0.081..4.540 rows=4,617 loops=3)

37. 0.036 0.226 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..451.21 rows=50 width=8) (actual time=0.049..0.226 rows=50 loops=3)

38. 0.040 0.040 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc_1 (cost=0.28..28.46 rows=50 width=4) (actual time=0.030..0.040 rows=50 loops=3)

  • Index Cond: ((companyid >= 1) AND (companyid <= 24,686))
  • Heap Fetches: 150
39. 0.150 0.150 ↑ 1.0 1 150 / 3

Index Only Scan using pk_ciqcompany on ciqcompany c_1 (cost=0.44..8.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=150)

  • Index Cond: (companyid = hc_1.companyid)
  • Heap Fetches: 150
40. 3.750 3.750 ↓ 1.8 92 150 / 3

Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp_1 (cost=0.44..192.84 rows=51 width=10) (actual time=0.009..0.075 rows=92 loops=150)

  • Index Cond: ((companyid = c_1.companyid) AND (periodtypeid = 4))
41. 97.072 97.072 ↑ 3.9 22 12,134 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic_1 (cost=0.57..314.38 rows=85 width=8) (actual time=0.008..0.024 rows=22 loops=12,134)

  • Index Cond: (financialinstanceid = fi_1.financialinstanceid)
  • Heap Fetches: 268,820
42. 268.820 268.820 ↓ 0.0 0 268,820 / 3

Index Scan using pk_ciqfincollection on ciqfincollection fc_1 (cost=0.57..8.01 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=268,820)

  • Index Cond: (financialcollectionid = ic_1.financialcollectionid)
  • Filter: (currencyid = 0)
  • Rows Removed by Filter: 1
43. 0.000 21,184.800 ↑ 66.4 5 60,528 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.393..1.050 rows=5 loops=60,528)

44. 1,089.504 1,089.504 ↑ 1.0 332 60,528 / 3

Seq Scan on hpsmnemonic hm_1 (cost=0.00..8.24 rows=332 width=21) (actual time=0.002..0.054 rows=332 loops=60,528)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
45. 20,095.296 20,095.296 ↓ 0.0 0 20,095,296 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd_1 (cost=0.71..8.73 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=20,095,296)

  • Index Cond: ((financialcollectionid = fc_1.financialcollectionid) AND (dataitemid = hm_1.dataitemid))
Planning time : 8.132 ms
Execution time : 108,550.866 ms