explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cLt4

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,400.702 ↓ 80.0 80 1

Unique (cost=66.35..66.36 rows=1 width=56) (actual time=1,400.688..1,400.702 rows=80 loops=1)

2. 0.058 1,400.691 ↓ 80.0 80 1

Sort (cost=66.35..66.35 rows=1 width=56) (actual time=1,400.688..1,400.691 rows=80 loops=1)

  • Sort Key: ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 31kB
3. 0.000 1,400.633 ↓ 80.0 80 1

Nested Loop (cost=61.86..66.34 rows=1 width=56) (actual time=1,400.598..1,400.633 rows=80 loops=1)

4. 0.003 23.202 ↑ 1.0 1 1

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

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

  • Index Cond: (eid = '6000097852'::bigint)
  • Heap Fetches: 1
6. 22.516 22.516 ↑ 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=22.515..22.516 rows=1 loops=1)

  • Index Cond: (eid = '6000097852'::bigint)
7. 0.017 1,377.409 ↓ 80.0 80 1

Unique (cost=61.15..61.15 rows=1 width=216) (actual time=1,377.388..1,377.409 rows=80 loops=1)

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

CTE p0

9. 0.111 256.919 ↓ 5.0 30 1

WindowAgg (cost=5.26..5.38 rows=6 width=28) (actual time=256.886..256.919 rows=30 loops=1)

10. 0.266 256.808 ↓ 5.0 30 1

Sort (cost=5.26..5.28 rows=6 width=28) (actual time=256.806..256.808 rows=30 loops=1)

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

12.          

CTE q1

13. 804.064 804.064 ↓ 8.4 236 1

Index Scan using "<idx1_tresumper>" on tresumper (cost=0.70..32.66 rows=28 width=201) (actual time=58.369..804.064 rows=236 loops=1)

  • Index Cond: ((estpermid = $0) AND (measure = 25) 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) AND (isparent = false))
  • Filter: (NOT isparent)
14. 0.147 1,377.392 ↓ 81.0 81 1

Sort (cost=23.11..23.11 rows=1 width=216) (actual time=1,377.388..1,377.392 rows=81 loops=1)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 31kB
15. 285.288 1,377.245 ↓ 81.0 81 1

Nested Loop (cost=1.95..23.10 rows=1 width=216) (actual time=1,124.855..1,377.245 rows=81 loops=1)

  • Join Filter: ((q1.effectivedate >= ad.effectivedate) AND ((q1.effectivedate < ad.expiredate) OR (ad.expiredate IS NULL)))
  • Rows Removed by Join Filter: 146
16. 0.397 1,091.957 ↓ 236.0 236 1

Hash Join (cost=1.52..20.39 rows=1 width=252) (actual time=1,083.753..1,091.957 rows=236 loops=1)

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

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

  • Index Cond: (codetype = 7)
18. 1,062.352 1,062.352 ↓ 236.0 236 1

Hash (cost=1.23..1.23 rows=1 width=242) (actual time=1,062.352..1,062.352 rows=236 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
19. 0.000 1,062.097 ↓ 236.0 236 1

Hash Join (cost=0.24..1.23 rows=1 width=242) (actual time=315.427..1,062.097 rows=236 loops=1)

  • Hash Cond: ((q1.perenddate = p0.targetperenddate) AND (q1.pertype = p0.pertype) AND (q1.fyemonth = p0.fyemonth) AND (q1.estpermid = p0.estpermid))
20. 804.466 804.466 ↓ 8.4 236 1

CTE Scan on q1 (cost=0.00..0.56 rows=28 width=226) (actual time=58.399..804.466 rows=236 loops=1)

21. 0.034 256.974 ↓ 4.8 29 1

Hash (cost=0.12..0.12 rows=6 width=32) (actual time=256.974..256.974 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 256.940 256.940 ↓ 5.0 30 1

CTE Scan on p0 (cost=0.00..0.12 rows=6 width=32) (actual time=256.893..256.940 rows=30 loops=1)

23. 284.852 284.852 ↑ 1.0 1 236

Index Scan using pkey_treperadvance on treperadvance ad (cost=0.43..2.66 rows=1 width=40) (actual time=1.207..1.207 rows=1 loops=236)

  • 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 : 258.284 ms
Execution time : 1,401.257 ms