explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CcKy

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 1.595 ↓ 0.0 0 1

Sort (cost=2,100.35..2,100.37 rows=10 width=52) (actual time=1.595..1.595 rows=0 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 25kB
2. 0.010 1.572 ↓ 0.0 0 1

Nested Loop (cost=126.54..2,100.18 rows=10 width=52) (actual time=1.572..1.572 rows=0 loops=1)

3. 0.034 0.464 ↓ 6.1 61 1

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

4. 0.229 0.229 ↑ 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.013..0.229 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.201 0.201 ↑ 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.003..0.003 rows=1 loops=67)

  • Index Cond: (eid = q.eid)
6. 0.061 1.098 ↓ 0.0 0 61

Limit (cost=125.82..125.82 rows=1 width=136) (actual time=0.018..0.018 rows=0 loops=61)

7.          

CTE p0

8. 0.012 0.178 ↑ 28.0 1 1

WindowAgg (cost=57.18..57.74 rows=28 width=78) (actual time=0.178..0.178 rows=1 loops=1)

9. 0.023 0.166 ↑ 14.0 2 1

Sort (cost=57.18..57.25 rows=28 width=46) (actual time=0.166..0.166 rows=2 loops=1)

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 32kB
10. 0.143 0.143 ↓ 3.4 96 1

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.038..0.143 rows=96 loops=1)

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

CTE q1

12. 0.000 0.488 ↓ 0.0 0 61

Limit (cost=0.70..24.46 rows=5 width=281) (actual time=0.008..0.008 rows=0 loops=61)

13. 0.488 0.488 ↓ 0.0 0 61

Index Scan Backward using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..24.46 rows=5 width=281) (actual time=0.008..0.008 rows=0 loops=61)

  • Index Cond: ((estpermid = $0) AND (measure = 27) AND (pertype = 3) AND ((effectivedate)::text >= '1990-10-02'::text) AND ((effectivedate)::text <= '2000-01-01'::text))
  • Filter: (NOT isparent)
14. 0.061 1.037 ↓ 0.0 0 61

Sort (cost=43.61..43.62 rows=1 width=136) (actual time=0.017..0.017 rows=0 loops=61)

  • Sort Key: ((q1.effectivedate)::date) DESC, q1.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
15. 0.000 0.976 ↓ 0.0 0 61

Nested Loop (cost=12.14..43.60 rows=1 width=136) (actual time=0.016..0.016 rows=0 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))
16. 0.122 0.976 ↓ 0.0 0 61

Hash Join (cost=11.59..34.98 rows=1 width=412) (actual time=0.016..0.016 rows=0 loops=61)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on trecode c (cost=10.50..32.09 rows=287 width=15) (never executed)

  • Recheck Cond: (codetype = 7)
18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on trecode_pkey (cost=0.00..10.43 rows=287 width=0) (never executed)

  • Index Cond: (codetype = 7)
19. 0.000 0.854 ↓ 0.0 0 61

Hash (cost=1.07..1.07 rows=1 width=406) (actual time=0.014..0.014 rows=0 loops=61)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 0.124 0.854 ↓ 0.0 0 61

Hash Join (cost=0.19..1.07 rows=1 width=406) (actual time=0.014..0.014 rows=0 loops=61)

  • Hash Cond: (((p0.targetperenddate)::text = (q1.perenddate)::text) AND (p0.pertype = q1.pertype) AND (p0.estpermid = q1.estpermid))
21. 0.181 0.181 ↑ 28.0 1 1

CTE Scan on p0 (cost=0.00..0.56 rows=28 width=138) (actual time=0.181..0.181 rows=1 loops=1)

22. 0.000 0.549 ↓ 0.0 0 61

Hash (cost=0.10..0.10 rows=5 width=392) (actual time=0.009..0.009 rows=0 loops=61)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 0.549 0.549 ↓ 0.0 0 61

CTE Scan on q1 (cost=0.00..0.10 rows=5 width=392) (actual time=0.009..0.009 rows=0 loops=61)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using treperadvance_pkey on treperadvance ad (cost=0.56..8.58 rows=1 width=98) (never executed)

  • 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.664 ms
Execution time : 1.820 ms