explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gfch

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 3.036 ↓ 43.0 43 1

Unique (cost=201.05..201.06 rows=1 width=52) (actual time=3.021..3.036 rows=43 loops=1)

2. 0.043 3.028 ↓ 43.0 43 1

Sort (cost=201.05..201.06 rows=1 width=52) (actual time=3.021..3.028 rows=43 loops=1)

  • Sort Key: ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 28kB
3. 0.055 2.985 ↓ 43.0 43 1

Nested Loop (cost=188.97..201.04 rows=1 width=52) (actual time=2.915..2.985 rows=43 loops=1)

4. 0.004 0.045 ↑ 1.0 1 1

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

5. 0.032 0.032 ↑ 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.032..0.032 rows=1 loops=1)

  • Index Cond: (eid = 1000046692)
  • Heap Fetches: 0
6. 0.009 0.009 ↑ 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.008..0.009 rows=1 loops=1)

  • Index Cond: (eid = 1000046692)
7. 0.014 2.885 ↓ 43.0 43 1

Unique (cost=188.25..188.26 rows=1 width=136) (actual time=2.863..2.885 rows=43 loops=1)

8.          

CTE p0

9. 0.051 0.204 ↓ 6.8 41 1

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

10. 0.049 0.153 ↓ 6.8 41 1

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

  • Sort Key: i.fyemonth, i.perindex
  • Sort Method: quicksort Memory: 30kB
11. 0.104 0.104 ↓ 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.035..0.104 rows=41 loops=1)

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

CTE q1

13. 0.215 0.215 ↓ 3.8 114 1

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..126.75 rows=30 width=281) (actual time=0.032..0.215 rows=114 loops=1)

  • Index Cond: ((estpermid = $0) AND (measure = 25) AND (pertype = 4) AND ((effectivedate)::text >= '1900-01-02'::text) AND ((effectivedate)::text <= '2100-01-01'::text))
  • Filter: (NOT isparent)
14. 0.082 2.871 ↓ 51.0 51 1

Sort (cost=43.85..43.85 rows=1 width=136) (actual time=2.862..2.871 rows=51 loops=1)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 32kB
15. 0.380 2.789 ↓ 51.0 51 1

Nested Loop (cost=12.37..43.84 rows=1 width=136) (actual time=1.015..2.789 rows=51 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: 57
16. 0.201 1.155 ↓ 114.0 114 1

Hash Join (cost=11.82..35.21 rows=1 width=420) (actual time=0.937..1.155 rows=114 loops=1)

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

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

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

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

  • Index Cond: (codetype = 7)
19. 0.066 0.842 ↓ 114.0 114 1

Hash (cost=1.30..1.30 rows=1 width=410) (actual time=0.842..0.842 rows=114 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
20. 0.211 0.776 ↓ 114.0 114 1

Hash Join (cost=0.24..1.30 rows=1 width=410) (actual time=0.357..0.776 rows=114 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. 0.297 0.297 ↓ 3.8 114 1

CTE Scan on q1 (cost=0.00..0.60 rows=30 width=394) (actual time=0.035..0.297 rows=114 loops=1)

22. 0.025 0.268 ↓ 6.7 40 1

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

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

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

24. 1.254 1.254 ↑ 1.0 1 114

Index Scan using treperadvance_pkey on treperadvance ad (cost=0.56..8.58 rows=1 width=100) (actual time=0.010..0.011 rows=1 loops=114)

  • 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 : 4.649 ms
Execution time : 3.353 ms