explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JK17

Settings
# exclusive inclusive rows x rows loops node
1. 1.415 946.310 ↓ 5,276.0 5,276 1

Sort (cost=5,436.35..5,436.35 rows=1 width=52) (actual time=945.992..946.310 rows=5,276 loops=1)

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

Nested Loop (cost=5,407.50..5,436.34 rows=1 width=52) (actual time=270.051..944.895 rows=5,276 loops=1)

3. 0.008 0.057 ↓ 2.0 2 1

Nested Loop (cost=0.72..29.53 rows=1 width=16) (actual time=0.032..0.057 rows=2 loops=1)

4. 0.025 0.025 ↑ 1.0 2 1

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

  • Index Cond: (eid = ANY ('{1000001437,1000001477}'::bigint[]))
  • Heap Fetches: 1
5. 0.024 0.024 ↑ 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.010..0.012 rows=1 loops=2)

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

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

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

CTE p0

8. 0.136 29.876 ↓ 4.4 124 2

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

9. 0.104 29.740 ↓ 4.4 124 2

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

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 36kB
10. 29.636 29.636 ↓ 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=5.855..14.818 rows=124 loops=2)

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

CTE q1

12. 498.358 498.358 ↓ 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=3.840..249.179 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. 25.110 935.280 ↓ 2,638.0 2,638 2

Nested Loop (cost=51.85..83.31 rows=1 width=136) (actual time=282.408..467.640 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. 8.794 569.576 ↓ 8,963.0 8,963 2

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

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

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

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

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

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

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

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

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

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

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

20. 0.078 30.068 ↓ 4.4 123 2

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

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

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

22. 340.594 340.594 ↑ 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.019..0.019 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.852 ms
Execution time : 948.234 ms