explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 14rx

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 5,392.939 ↓ 410.0 410 1

Unique (cost=1,094.22..1,094.23 rows=1 width=56) (actual time=5,392.864..5,392.939 rows=410 loops=1)

2. 0.000 5,392.886 ↓ 410.0 410 1

Sort (cost=1,094.22..1,094.22 rows=1 width=56) (actual time=5,392.864..5,392.886 rows=410 loops=1)

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

Nested Loop (cost=1,089.73..1,094.21 rows=1 width=56) (actual time=5,392.603..5,392.790 rows=410 loops=1)

4. 0.002 0.026 ↑ 1.0 1 1

Nested Loop (cost=0.72..5.16 rows=1 width=16) (actual time=0.022..0.026 rows=1 loops=1)

5. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.42..2.64 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1)

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

  • Index Cond: (eid = 1000046692)
7. 0.083 5,392.692 ↓ 410.0 410 1

Unique (cost=1,089.01..1,089.02 rows=1 width=216) (actual time=5,392.573..5,392.692 rows=410 loops=1)

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

CTE p0

9. 0.028 0.149 ↓ 6.8 41 1

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

10. 0.027 0.121 ↓ 6.8 41 1

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

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

12.          

CTE q1

13. 5,131.175 5,131.175 ↓ 1.9 1,693 1

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..1,030.18 rows=895 width=201) (actual time=33.351..5,131.175 rows=1,693 loops=1)

  • Index Cond: ((estpermid = $0) AND (measure = 1) AND (pertype = 4) AND (effectivedate >= '1900-01-02 00:00:00'::timestamp without time zone) AND (effectivedate <= '2100-01-01 00:00:00'::timestamp without time zone))
  • Filter: (NOT isparent)
14. 0.350 5,392.609 ↓ 617.0 617 1

Sort (cost=53.45..53.46 rows=1 width=216) (actual time=5,392.572..5,392.609 rows=617 loops=1)

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

Nested Loop (cost=32.30..53.44 rows=1 width=216) (actual time=5,222.764..5,392.259 rows=617 loops=1)

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

Hash Join (cost=31.87..50.74 rows=1 width=252) (actual time=5,141.283..5,142.382 rows=1,693 loops=1)

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

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

  • Index Cond: (codetype = 7)
18. 5,141.244 5,141.244 ↓ 1,693.0 1,693 1

Hash (cost=31.58..31.58 rows=1 width=242) (actual time=5,141.244..5,141.244 rows=1,693 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 215kB
19. 0.000 5,139.072 ↓ 1,693.0 1,693 1

Hash Join (cost=0.24..31.58 rows=1 width=242) (actual time=33.553..5,139.072 rows=1,693 loops=1)

  • Hash Cond: ((q1.perenddate = p0.targetperenddate) AND (q1.pertype = p0.pertype) AND (q1.fyemonth = p0.fyemonth) AND (q1.estpermid = p0.estpermid))
20. 5,134.116 5,134.116 ↓ 1.9 1,693 1

CTE Scan on q1 (cost=0.00..17.90 rows=895 width=226) (actual time=33.353..5,134.116 rows=1,693 loops=1)

21. 0.011 0.177 ↓ 6.7 40 1

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

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

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

23. 248.871 248.871 ↑ 1.0 1 1,693

Index Scan using pkey_treperadvance on treperadvance ad (cost=0.43..2.66 rows=1 width=40) (actual time=0.146..0.147 rows=1 loops=1,693)

  • Index Cond: ((perenddate = p0.currperenddate) AND (estpermid = p0.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength) AND (fyemonth = p0.fyemonth))
Planning time : 1.573 ms
Execution time : 5,394.068 ms