explain.depesz.com

PostgreSQL's explain analyze made readable

Result: flC8

Settings
# exclusive inclusive rows x rows loops node
1. 0.090 1,673.584 ↓ 410.0 410 1

Unique (cost=1,068.27..1,068.28 rows=1 width=56) (actual time=1,673.456..1,673.584 rows=410 loops=1)

2. 0.274 1,673.494 ↓ 410.0 410 1

Sort (cost=1,068.27..1,068.27 rows=1 width=56) (actual time=1,673.455..1,673.494 rows=410 loops=1)

  • Sort Key: ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 57kB
3. 0.140 1,673.220 ↓ 410.0 410 1

Nested Loop (cost=1,063.78..1,068.26 rows=1 width=56) (actual time=1,672.881..1,673.220 rows=410 loops=1)

4. 0.004 0.338 ↑ 1.0 1 1

Nested Loop (cost=0.73..5.17 rows=1 width=16) (actual time=0.332..0.338 rows=1 loops=1)

5. 0.319 0.319 ↑ 1.0 1 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.44..2.65 rows=1 width=8) (actual time=0.317..0.319 rows=1 loops=1)

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

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map (cost=0.29..2.51 rows=1 width=16) (actual time=0.012..0.015 rows=1 loops=1)

  • Index Cond: (eid = 1000046692)
7. 0.146 1,672.742 ↓ 410.0 410 1

Unique (cost=1,063.05..1,063.06 rows=1 width=216) (actual time=1,672.540..1,672.742 rows=410 loops=1)

8.          

CTE p0

9. 0.107 0.448 ↓ 6.8 41 1

WindowAgg (cost=5.26..5.38 rows=6 width=28) (actual time=0.360..0.448 rows=41 loops=1)

10. 0.216 0.341 ↓ 6.8 41 1

Sort (cost=5.26..5.28 rows=6 width=28) (actual time=0.336..0.341 rows=41 loops=1)

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

Index Scan using treperindex_periodicity_pertype_perlength_estpermid_perendd_idx on treperindex i (cost=0.43..5.19 rows=6 width=28) (actual time=0.032..0.125 rows=41 loops=1)

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

CTE q1

13. 1,357.937 1,357.937 ↓ 423.2 1,693 1

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..1,034.84 rows=4 width=201) (actual time=7.559..1,357.937 rows=1,693 loops=1)

  • Index Cond: ((estpermid = $0) AND (measure = 1) AND (pertype = 4))
  • Filter: ((NOT isparent) AND ((effectivedate)::date >= '1900-01-02'::date) AND ((effectivedate)::date <= '2100-01-01'::date))
14. 0.843 1,672.596 ↓ 617.0 617 1

Sort (cost=22.84..22.84 rows=1 width=216) (actual time=1,672.539..1,672.596 rows=617 loops=1)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 73kB
15. 3.561 1,671.753 ↓ 617.0 617 1

Nested Loop (cost=1.21..22.83 rows=1 width=216) (actual time=1,598.804..1,671.753 rows=617 loops=1)

  • Join Filter: (((q1.effectivedate)::date >= (ad.effectivedate)::date) AND (((q1.effectivedate)::date < (ad.expiredate)::date) OR (ad.expiredate IS NULL)))
  • Rows Removed by Join Filter: 1020
16. 2.655 1,370.224 ↓ 1,693.0 1,693 1

Hash Join (cost=0.65..19.69 rows=1 width=252) (actual time=1,365.356..1,370.224 rows=1,693 loops=1)

  • Hash Cond: (((q1.perenddate)::date = (p0.targetperenddate)::date) AND (q1.pertype = p0.pertype) AND (q1.fyemonth = p0.fyemonth) AND (q1.estpermid = p0.estpermid))
17. 2.157 1,367.027 ↓ 282.2 1,693 1

Hash Join (cost=0.41..19.33 rows=6 width=236) (actual time=1,364.760..1,367.027 rows=1,693 loops=1)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
18. 0.191 0.191 ↑ 1.0 287 1

Index Scan using pkey_trecode on trecode c (cost=0.28..17.35 rows=287 width=15) (actual time=0.037..0.191 rows=287 loops=1)

  • Index Cond: (codetype = 7)
19. 2.130 1,364.679 ↓ 423.2 1,693 1

Hash (cost=0.08..0.08 rows=4 width=226) (actual time=1,364.679..1,364.679 rows=1,693 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 175kB
20. 1,362.549 1,362.549 ↓ 423.2 1,693 1

CTE Scan on q1 (cost=0.00..0.08 rows=4 width=226) (actual time=7.563..1,362.549 rows=1,693 loops=1)

21. 0.054 0.542 ↓ 6.7 40 1

Hash (cost=0.12..0.12 rows=6 width=32) (actual time=0.542..0.542 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.488 0.488 ↓ 6.8 41 1

CTE Scan on p0 (cost=0.00..0.12 rows=6 width=32) (actual time=0.367..0.488 rows=41 loops=1)

23. 297.968 297.968 ↑ 1.0 1 1,693

Index Only Scan using "<idx1_treperadvance>" on treperadvance ad (cost=0.56..3.09 rows=1 width=40) (actual time=0.170..0.176 rows=1 loops=1,693)

  • Index Cond: ((estpermid = p0.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength) AND (fyemonth = p0.fyemonth))
  • Filter: ((p0.currperenddate)::date = (perenddate)::date)
  • Rows Removed by Filter: 34
  • Heap Fetches: 59255
Planning time : 161.221 ms
Execution time : 1,674.716 ms