explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6qjo

Settings
# exclusive inclusive rows x rows loops node
1. 1.552 304.998 ↓ 5,276.0 5,276 1

Sort (cost=5,432.35..5,432.35 rows=1 width=52) (actual time=304.677..304.998 rows=5,276 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 605kB
2. 3.917 303.446 ↓ 5,276.0 5,276 1

Nested Loop (cost=5,407.50..5,432.34 rows=1 width=52) (actual time=82.094..303.446 rows=5,276 loops=1)

3. 0.008 0.041 ↓ 2.0 2 1

Nested Loop (cost=0.72..25.53 rows=1 width=16) (actual time=0.019..0.041 rows=2 loops=1)

4. 0.021 0.021 ↑ 1.0 2 1

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

  • Index Cond: (eid = ANY ('{1000001437,1000001477}'::bigint[]))
  • Heap Fetches: 0
5. 0.012 0.012 ↑ 1.0 1 2

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map (cost=0.29..8.31 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=2)

  • Index Cond: (eid = q.eid)
6. 8.940 299.488 ↓ 2,638.0 2,638 2

Sort (cost=5,406.78..5,406.79 rows=1 width=136) (actual time=149.462..149.744 rows=2,638 loops=2)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 644kB
7.          

CTE p0

8. 0.182 0.574 ↓ 4.4 124 2

WindowAgg (cost=57.18..57.74 rows=28 width=78) (actual time=0.193..0.287 rows=124 loops=2)

9. 0.072 0.392 ↓ 4.4 124 2

Sort (cost=57.18..57.25 rows=28 width=46) (actual time=0.185..0.196 rows=124 loops=2)

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 36kB
10. 0.320 0.320 ↓ 4.4 124 2

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.029..0.160 rows=124 loops=2)

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

CTE q1

12. 21.244 21.244 ↓ 7.1 8,963 2

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper (cost=0.70..5,265.72 rows=1,271 width=281) (actual time=0.065..10.622 rows=8,963 loops=2)

  • Index Cond: ((estpermid = $0) AND (measure = 9) AND (pertype = 3) AND ((effectivedate)::text >= '1990-10-02'::text) AND ((effectivedate)::text <= '2020-01-01'::text))
  • Filter: (NOT isparent)
13. 53.354 290.548 ↓ 2,638.0 2,638 2

Nested Loop (cost=51.85..83.31 rows=1 width=136) (actual time=30.492..145.274 rows=2,638 loops=2)

  • 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))
  • Rows Removed by Join Filter: 6178
14. 14.998 75.860 ↓ 8,963.0 8,963 2

Hash Join (cost=51.30..74.69 rows=1 width=412) (actual time=30.420..37.930 rows=8,963 loops=2)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
15. 0.070 0.158 ↑ 1.0 287 2

Bitmap Heap Scan on trecode c (cost=10.50..32.09 rows=287 width=15) (actual time=0.053..0.079 rows=287 loops=2)

  • Recheck Cond: (codetype = 7)
  • Heap Blocks: exact=4
16. 0.088 0.088 ↑ 1.0 287 2

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

  • Index Cond: (codetype = 7)
17. 8.610 60.704 ↓ 8,963.0 8,963 2

Hash (cost=40.78..40.78 rows=1 width=406) (actual time=30.352..30.352 rows=8,963 loops=2)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2182kB
18. 15.820 52.094 ↓ 8,963.0 8,963 2

Hash Join (cost=1.05..40.78 rows=1 width=406) (actual time=0.596..26.047 rows=8,963 loops=2)

  • Hash Cond: (((q1.perenddate)::text = (p0.targetperenddate)::text) AND (q1.pertype = p0.pertype) AND (q1.estpermid = p0.estpermid))
19. 35.478 35.478 ↓ 7.1 8,963 2

CTE Scan on q1 (cost=0.00..25.42 rows=1,271 width=392) (actual time=0.068..17.739 rows=8,963 loops=2)

20. 0.106 0.796 ↓ 4.4 123 2

Hash (cost=0.56..0.56 rows=28 width=138) (actual time=0.398..0.398 rows=123 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
21. 0.690 0.690 ↓ 4.4 124 2

CTE Scan on p0 (cost=0.00..0.56 rows=28 width=138) (actual time=0.195..0.345 rows=124 loops=2)

22. 161.334 161.334 ↑ 1.0 1 17,926

Index Scan using treperadvance_pkey on treperadvance ad (cost=0.56..8.58 rows=1 width=98) (actual time=0.009..0.009 rows=1 loops=17,926)

  • 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 : 4.065 ms
Execution time : 307.265 ms