explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x0BD

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=29,013,949.70..29,015,832.03 rows=83,659 width=116) (actual rows= loops=)

2.          

CTE overrides

3. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.66 rows=53 width=116) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=29,013,949.04..29,014,158.18 rows=83,659 width=116) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".exid, "*SELECT* 1".warpname, "*SELECT* 1".indexticker, "*SELECT* 1".indexname, (1), (("*SELECT* 1".indexventype)::integer), "*SELECT* 1".indexvencode, (1)
5. 0.000 0.000 ↓ 0.0

Append (cost=1.19..29,004,093.43 rows=83,659 width=116) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1.19..325.23 rows=29 width=94) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on idxinfo (cost=1.19..324.87 rows=29 width=92) (actual rows= loops=)

  • Filter: ((type_ >= 1) AND (type_ <= 4) AND (NOT (hashed SubPlan 5)))
8.          

SubPlan (for Seq Scan)

9. 0.000 0.000 ↓ 0.0

CTE Scan on overrides overrides_4 (cost=0.00..1.19 rows=1 width=4) (actual rows= loops=)

  • Filter: (indexventype = 1)
10. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1.88..28,907,263.87 rows=58,748 width=116) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Unique (cost=1.88..28,906,676.39 rows=58,748 width=116) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.88..28,906,529.52 rows=58,748 width=116) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.61..28,886,987.01 rows=58,748 width=48) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using ds2equityindex_vw1_pkey on ds2equityindex_vw1 q (cost=1.61..4,060.70 rows=58,748 width=48) (actual rows= loops=)

  • Filter: (NOT (hashed SubPlan 4))
15.          

SubPlan (for Index Scan)

16. 0.000 0.000 ↓ 0.0

CTE Scan on overrides overrides_3 (cost=0.00..1.19 rows=1 width=4) (actual rows= loops=)

  • Filter: (indexventype = 33)
17. 0.000 0.000 ↓ 0.0

Limit (cost=0.00..491.62 rows=1 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on ds2indexdata (cost=0.00..6,630,001.00 rows=13,486 width=4) (actual rows= loops=)

  • Filter: (dsindexcode = q.dsindexcode)
19. 0.000 0.000 ↓ 0.0

Index Scan using pkey_ds2region on ds2region r (cost=0.27..0.29 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (((region)::text = (q.region)::text) AND (regcodetypeid = q.regcodetypeid))
20. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=1.34..3.61 rows=1 width=116) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using idxtrinfo_2 on idxtrinfo (cost=1.34..3.60 rows=1 width=116) (actual rows= loops=)

  • Index Cond: (rettypecode = 'T'::bpchar)
  • Filter: ((NOT (hashed SubPlan 3)) AND (ctryregcode <> ALL ('{14,20,21}'::integer[])))
22.          

SubPlan (for Index Scan)

23. 0.000 0.000 ↓ 0.0

CTE Scan on overrides overrides_2 (cost=0.00..1.19 rows=1 width=4) (actual rows= loops=)

  • Filter: (indexventype = 44)
24. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=95,794.45..96,477.16 rows=24,826 width=116) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Subquery Scan on m (cost=95,794.45..96,228.90 rows=24,826 width=116) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Unique (cost=95,794.45..95,980.64 rows=24,826 width=192) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=95,794.45..95,856.51 rows=24,826 width=192) (actual rows= loops=)

  • Sort Key: (regexp_replace((ms2idxinfo.name_)::text, '[^\w]'::text, '_'::text, 'g'::text)), sector.value_, ms2idxinfo.enddate DESC
28. 0.000 0.000 ↓ 0.0

WindowAgg (cost=87,270.83..92,639.45 rows=24,826 width=192) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=87,270.83..87,332.89 rows=24,826 width=95) (actual rows= loops=)

  • Sort Key: (regexp_replace(regexp_replace(replace(replace(replace(replace(replace(upper((ms2idxinfo.name_)::text), 'WRLD'::text, 'WORLD'::text), 'O/'::text, 'WORLD'::text), ' EX'::text, 'X'::text), 'ALL '::text, ''::text), 'INDEX'::text, ''::text), '\(.*\)'::text, ''::text, ''::text), '[^\w]'::text, ''::text, 'g'::text)), sector.value_ NULLS FIRST
30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=48,269.24..85,458.59 rows=24,826 width=95) (actual rows= loops=)

  • Hash Cond: (ms2idxinfo.isoctrycode = (d.region)::bpchar)
31. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=48,256.84..84,817.71 rows=24,826 width=49) (actual rows= loops=)

  • Hash Cond: (sector.mscicode = ms2idxinfo.mscicode)
32. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ms2idxi sector (cost=46,747.68..83,217.03 rows=10,459 width=8) (actual rows= loops=)

  • Recheck Cond: (enddate IS NULL)
  • Filter: (item = ANY ('{253,254,671,708}'::integer[]))
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ms2idxi_1 (cost=0.00..46,745.07 rows=506,890 width=0) (actual rows= loops=)

  • Index Cond: (enddate IS NULL)
34. 0.000 0.000 ↓ 0.0

Hash (cost=1,198.83..1,198.83 rows=24,826 width=45) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on ms2idxinfo (cost=1.19..1,198.83 rows=24,826 width=45) (actual rows= loops=)

  • Filter: (NOT (hashed SubPlan 2))
36.          

SubPlan (for Seq Scan)

37. 0.000 0.000 ↓ 0.0

CTE Scan on overrides overrides_1 (cost=0.00..1.19 rows=1 width=4) (actual rows= loops=)

  • Filter: (indexventype = 48)
38. 0.000 0.000 ↓ 0.0

Hash (cost=9.34..9.34 rows=245 width=21) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on ds2region d (cost=0.00..9.34 rows=245 width=21) (actual rows= loops=)

  • Filter: (regcodetypeid = 1)
40. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=11.03..21.97 rows=2 width=502) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=11.03..21.95 rows=2 width=502) (actual rows= loops=)

  • Hash Cond: (((d_1.region)::bpchar)::text = "substring"((i.indexkey)::text, 20, 2))
42. 0.000 0.000 ↓ 0.0

Seq Scan on ds2region d_1 (cost=0.00..9.34 rows=245 width=21) (actual rows= loops=)

  • Filter: (regcodetypeid = 1)
43. 0.000 0.000 ↓ 0.0

Hash (cost=11.00..11.00 rows=2 width=938) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on idxbmiinfo i (cost=0.00..11.00 rows=2 width=938) (actual rows= loops=)

  • Filter: (returncode = ANY ('{1,4}'::integer[]))
45. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=0.00..1.59 rows=53 width=116) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

CTE Scan on overrides (cost=0.00..1.06 rows=53 width=116) (actual rows= loops=)