explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FGWh

Settings
# exclusive inclusive rows x rows loops node
1. 14.170 60,275.981 ↓ 9,137.8 82,240 1

Unique (cost=2,790.03..2,790.10 rows=9 width=52) (actual time=60,249.230..60,275.981 rows=82,240 loops=1)

2. 94.235 60,261.811 ↓ 9,137.8 82,240 1

Sort (cost=2,790.03..2,790.05 rows=9 width=52) (actual time=60,249.229..60,261.811 rows=82,240 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: external sort Disk: 3536kB
3. 79.833 60,167.576 ↓ 9,137.8 82,240 1

Nested Loop (cost=220.11..2,789.89 rows=9 width=52) (actual time=368.624..60,167.576 rows=82,240 loops=1)

4. 0.202 1.401 ↓ 6.8 61 1

Nested Loop (cost=0.72..815.09 rows=9 width=16) (actual time=0.021..1.401 rows=61 loops=1)

5. 0.596 0.596 ↑ 1.0 67 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.42..297.65 rows=67 width=8) (actual time=0.015..0.596 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: 0
6. 0.603 0.603 ↑ 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.007..0.009 rows=1 loops=67)

  • Index Cond: (eid = q.eid)
7. 31.049 60,086.342 ↓ 1,348.0 1,348 61

Unique (cost=219.39..219.40 rows=1 width=72) (actual time=984.193..985.022 rows=1,348 loops=61)

8.          

CTE p0

9. 30.927 51.423 ↓ 4.6 130 61

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

10. 7.137 20.496 ↓ 4.6 130 61

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

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 26kB
11. 13.359 13.359 ↓ 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=0.032..0.219 rows=130 loops=61)

  • Index Cond: ((periodicity = 3) AND (pertype = 3) AND (perlength = 3) AND (estpermid = $0))
12. 276.086 60,055.293 ↓ 2,461.0 2,461 61

Sort (cost=161.65..161.65 rows=1 width=72) (actual time=984.187..984.513 rows=2,461 loops=61)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 42kB
13. 1,232.849 59,779.207 ↓ 2,461.0 2,461 61

Nested Loop (cost=130.18..161.64 rows=1 width=72) (actual time=870.288..979.987 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))
  • Rows Removed by Join Filter: 7628
14. 422.691 53,475.406 ↓ 10,391.0 10,391 61

Hash Join (cost=129.62..153.02 rows=1 width=171) (actual time=869.692..876.646 rows=10,391 loops=61)

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

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

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

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

  • Index Cond: (codetype = 7)
17. 345.870 53,046.393 ↓ 10,391.0 10,391 61

Hash (cost=119.11..119.11 rows=1 width=165) (actual time=869.613..869.613 rows=10,391 loops=61)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
18. 357.033 52,700.523 ↓ 10,391.0 10,391 61

Nested Loop (cost=0.70..119.11 rows=1 width=165) (actual time=144.155..863.943 rows=10,391 loops=61)

19. 74.298 74.298 ↓ 130.0 130 61

CTE Scan on p0 (cost=0.00..0.63 rows=1 width=138) (actual time=0.268..1.218 rows=130 loops=61)

  • Filter: (estpermid = map.estpermid)
20. 52,269.192 52,269.192 ↓ 20.0 80 7,934

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper q1 (cost=0.70..118.44 rows=4 width=95) (actual time=3.033..6.588 rows=80 loops=7,934)

  • Index Cond: ((estpermid = map.estpermid) AND (measure = 9) AND (pertype = p0.pertype) AND ((effectivedate)::text >= '1990-10-02'::text) AND ((effectivedate)::text <= '2020-01-01'::text) AND ((perenddate)::text = (p0.targetperenddate)::text))
  • Filter: (NOT isparent)
21. 5,070.952 5,070.952 ↑ 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.008..0.008 rows=1 loops=633,869)

  • Index Cond: (((perenddate)::text = (p0.currperenddate)::text) AND (estpermid = map.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength))
Planning time : 6.242 ms
Execution time : 60,282.503 ms