explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cQCE

Settings
# exclusive inclusive rows x rows loops node
1. 127.622 29,603.485 ↓ 15,009.4 150,094 1

Sort (cost=54,910.00..54,910.02 rows=10 width=52) (actual time=29,586.678..29,603.485 rows=150,094 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: external sort Disk: 6456kB
2. 98.719 29,475.863 ↓ 15,009.4 150,094 1

Nested Loop (cost=5,407.50..54,909.83 rows=10 width=52) (actual time=39.851..29,475.863 rows=150,094 loops=1)

3. 0.210 1.435 ↓ 6.1 61 1

Nested Loop (cost=0.72..841.71 rows=10 width=16) (actual time=0.020..1.435 rows=61 loops=1)

4. 0.689 0.689 ↑ 1.0 67 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.42..324.27 rows=67 width=8) (actual time=0.014..0.689 rows=67 loops=1)

  • Index Cond: (eid = ANY ('{1000001437,1000001477,1000002885,1000003571,1000003825,1000004547,1000004760,1000005677,1000006027,1000008156,1000009147,1000009965,1000011061,1000012762,1000013790,1000014169,1000014316,1000014360,1000015031,1000020001,1000022092,1000022745,1000024491,1000025075,1000027831,1000027982,1000029629,1000029693,1000029734,1000030655,1000030899,1000033402,1000033775,1000033894,1000035705,1000036411,1000036799,1000036988,1000037953,1000038647,1000039943,1000044499,1000044517,1000045266,1000045939,1000046692,1000047842,1000049751,1000050689,1000053269,1000055299,1000057100,1000057508,1000061840,1000068871,1000075225,1000075580,1000075613,1000077787,1000078331,1000078373,1000079732,1000080721,1000080738,1000340028,1011176481,1011195020}'::bigint[]))
  • Heap Fetches: 33
5. 0.536 0.536 ↑ 1.0 1 67

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map (cost=0.29..7.71 rows=1 width=16) (actual time=0.006..0.008 rows=1 loops=67)

  • Index Cond: (eid = q.eid)
6. 197.396 29,375.709 ↓ 2,461.0 2,461 61

Sort (cost=5,406.78..5,406.79 rows=1 width=136) (actual time=481.339..481.569 rows=2,461 loops=61)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 42kB
7.          

CTE p0

8. 4.087 659.044 ↓ 4.6 130 61

WindowAgg (cost=57.18..57.74 rows=28 width=78) (actual time=10.735..10.804 rows=130 loops=61)

9. 2.623 654.957 ↓ 4.6 130 61

Sort (cost=57.18..57.25 rows=28 width=46) (actual time=10.727..10.737 rows=130 loops=61)

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 26kB
10. 652.334 652.334 ↓ 4.6 130 61

Index Scan using treperindex_periodicity_pertype_perlength_estpermid_perendd_idx on treperindex i (cost=0.56..56.51 rows=28 width=46) (actual time=2.923..10.694 rows=130 loops=61)

  • Index Cond: ((periodicity = 3) AND (pertype = 3) AND (perlength = 3) AND (estpermid = $0))
11.          

CTE q1

12. 16,964.283 16,964.283 ↓ 8.2 10,391 61

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..5,265.72 rows=1,271 width=281) (actual time=3.911..278.103 rows=10,391 loops=61)

  • Index Cond: ((estpermid = $0) AND (measure = 9) AND (pertype = 3) AND ((effectivedate)::text >= '1990-10-02'::text) AND ((effectivedate)::text <= '2020-01-01'::text))
  • Filter: (NOT isparent)
13. 644.561 29,178.313 ↓ 2,461.0 2,461 61

Nested Loop (cost=51.85..83.31 rows=1 width=136) (actual time=309.536..478.333 rows=2,461 loops=61)

  • Join Filter: (((q1.effectivedate)::text >= (ad.effectivedate)::text) AND (((q1.effectivedate)::text < (ad.expiredate)::text) OR (ad.expiredate IS NULL)) AND (q1.pertype = ad.pertype) AND (q1.estpermid = ad.estpermid))
  • Rows Removed by Join Filter: 7628
14. 311.819 19,025.717 ↓ 10,391.0 10,391 61

Hash Join (cost=51.30..74.69 rows=1 width=412) (actual time=306.778..311.897 rows=10,391 loops=61)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
15. 1.972 4.466 ↑ 1.0 287 58

Bitmap Heap Scan on trecode c (cost=10.50..32.09 rows=287 width=15) (actual time=0.053..0.077 rows=287 loops=58)

  • Recheck Cond: (codetype = 7)
  • Heap Blocks: exact=116
16. 2.494 2.494 ↑ 1.0 287 58

Bitmap Index Scan on trecode_pkey (cost=0.00..10.43 rows=287 width=0) (actual time=0.043..0.043 rows=287 loops=58)

  • Index Cond: (codetype = 7)
17. 238.327 18,709.432 ↓ 10,391.0 10,391 61

Hash (cost=40.78..40.78 rows=1 width=406) (actual time=306.712..306.712 rows=10,391 loops=61)

  • Buckets: 1024 Batches: 1 Memory Usage: 107kB
18. 480.741 18,471.105 ↓ 10,391.0 10,391 61

Hash Join (cost=1.05..40.78 rows=1 width=406) (actual time=15.479..302.805 rows=10,391 loops=61)

  • Hash Cond: (((q1.perenddate)::text = (p0.targetperenddate)::text) AND (q1.pertype = p0.pertype) AND (q1.estpermid = p0.estpermid))
19. 17,324.976 17,324.976 ↓ 8.2 10,391 61

CTE Scan on q1 (cost=0.00..25.42 rows=1,271 width=392) (actual time=3.915..284.016 rows=10,391 loops=61)

20. 2.684 665.388 ↓ 4.6 129 61

Hash (cost=0.56..0.56 rows=28 width=138) (actual time=10.908..10.908 rows=129 loops=61)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 662.704 662.704 ↓ 4.6 130 61

CTE Scan on p0 (cost=0.00..0.56 rows=28 width=138) (actual time=10.738..10.864 rows=130 loops=61)

22. 9,508.035 9,508.035 ↑ 1.0 1 633,869

Index Scan using treperadvance_pkey on treperadvance ad (cost=0.56..8.58 rows=1 width=98) (actual time=0.015..0.015 rows=1 loops=633,869)

  • Index Cond: (((perenddate)::text = (p0.currperenddate)::text) AND (estpermid = p0.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength))
Planning time : 3.407 ms
Execution time : 29,611.506 ms