explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jHcw

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 132.262 ↓ 408.0 408 1

Unique (cost=3,708.89..3,708.90 rows=1 width=52) (actual time=132.178..132.262 rows=408 loops=1)

2. 0.107 132.202 ↓ 408.0 408 1

Sort (cost=3,708.89..3,708.90 rows=1 width=52) (actual time=132.178..132.202 rows=408 loops=1)

  • Sort Key: ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 56kB
3. 0.308 132.095 ↓ 408.0 408 1

Nested Loop (cost=3,696.80..3,708.88 rows=1 width=52) (actual time=131.661..132.095 rows=408 loops=1)

4. 0.002 0.035 ↑ 1.0 1 1

Nested Loop (cost=0.72..12.76 rows=1 width=16) (actual time=0.031..0.035 rows=1 loops=1)

5. 0.026 0.026 ↑ 1.0 1 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.42..4.44 rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=1)

  • Index Cond: (eid = 1000046692)
  • Heap Fetches: 0
6. 0.007 0.007 ↑ 1.0 1 1

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map (cost=0.29..8.31 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=1)

  • Index Cond: (eid = 1000046692)
7. 0.081 131.752 ↓ 408.0 408 1

Unique (cost=3,696.09..3,696.09 rows=1 width=136) (actual time=131.622..131.752 rows=408 loops=1)

8.          

CTE p0

9. 0.032 0.146 ↓ 6.8 41 1

WindowAgg (cost=17.53..17.65 rows=6 width=80) (actual time=0.120..0.146 rows=41 loops=1)

10. 0.040 0.114 ↓ 6.8 41 1

Sort (cost=17.53..17.55 rows=6 width=48) (actual time=0.111..0.114 rows=41 loops=1)

  • Sort Key: i.fyemonth, i.perindex
  • Sort Method: quicksort Memory: 30kB
11. 0.074 0.074 ↓ 6.8 41 1

Index Scan using treperindex_periodicity_pertype_perlength_estpermid_perendd_idx on treperindex i (cost=0.56..17.46 rows=6 width=48) (actual time=0.027..0.074 rows=41 loops=1)

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

CTE q1

13. 83.778 83.778 ↓ 1.9 1,691 1

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..3,605.19 rows=870 width=281) (actual time=4.988..83.778 rows=1,691 loops=1)

  • Index Cond: ((estpermid = $0) AND (measure = 1) AND (pertype = 4) AND ((effectivedate)::text >= '1900-01-02'::text) AND ((effectivedate)::text <= '2100-01-01'::text))
  • Filter: (NOT isparent)
14. 0.649 131.671 ↓ 615.0 615 1

Sort (cost=73.25..73.25 rows=1 width=136) (actual time=131.621..131.671 rows=615 loops=1)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 111kB
15. 2.710 131.022 ↓ 615.0 615 1

Nested Loop (cost=41.77..73.24 rows=1 width=136) (actual time=93.953..131.022 rows=615 loops=1)

  • Join Filter: (((q1.effectivedate)::text >= (ad.effectivedate)::text) AND (((q1.effectivedate)::text < (ad.expiredate)::text) OR (ad.expiredate IS NULL)))
  • Rows Removed by Join Filter: 1020
16. 0.930 87.728 ↓ 1,691.0 1,691 1

Hash Join (cost=41.22..64.61 rows=1 width=420) (actual time=86.799..87.728 rows=1,691 loops=1)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
17. 0.029 0.074 ↑ 1.0 287 1

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

  • Recheck Cond: (codetype = 7)
  • Heap Blocks: exact=2
18. 0.045 0.045 ↑ 1.0 287 1

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

  • Index Cond: (codetype = 7)
19. 0.696 86.724 ↓ 1,691.0 1,691 1

Hash (cost=30.70..30.70 rows=1 width=410) (actual time=86.724..86.724 rows=1,691 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 281kB
20. 1.221 86.028 ↓ 1,691.0 1,691 1

Hash Join (cost=0.24..30.70 rows=1 width=410) (actual time=5.222..86.028 rows=1,691 loops=1)

  • Hash Cond: (((q1.perenddate)::text = (p0.targetperenddate)::text) AND (q1.pertype = p0.pertype) AND (q1.fyemonth = p0.fyemonth) AND (q1.estpermid = p0.estpermid))
21. 84.601 84.601 ↓ 1.9 1,691 1

CTE Scan on q1 (cost=0.00..17.40 rows=870 width=394) (actual time=4.991..84.601 rows=1,691 loops=1)

22. 0.041 0.206 ↓ 6.7 40 1

Hash (cost=0.12..0.12 rows=6 width=140) (actual time=0.206..0.206 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 0.165 0.165 ↓ 6.8 41 1

CTE Scan on p0 (cost=0.00..0.12 rows=6 width=140) (actual time=0.122..0.165 rows=41 loops=1)

24. 40.584 40.584 ↑ 1.0 1 1,691

Index Scan using treperadvance_pkey on treperadvance ad (cost=0.56..8.58 rows=1 width=100) (actual time=0.024..0.024 rows=1 loops=1,691)

  • Index Cond: (((perenddate)::text = (p0.currperenddate)::text) AND (estpermid = p0.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength) AND (fyemonth = p0.fyemonth))
Planning time : 3.223 ms
Execution time : 132.638 ms