explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nly2

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,023,729.211 ↑ 1.4 48 1

Unique (cost=12,880.51..12,881.01 rows=67 width=52) (actual time=1,023,729.187..1,023,729.211 rows=48 loops=1)

2.          

CTE map

3. 0.039 0.356 ↑ 1.1 61 1

Unique (cost=0.29..476.40 rows=67 width=24) (actual time=0.009..0.356 rows=61 loops=1)

4. 0.317 0.317 ↑ 1.1 61 1

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map_1 (cost=0.29..476.24 rows=67 width=24) (actual time=0.008..0.317 rows=61 loops=1)

  • Index Cond: (eid = ANY ('{1000001437,1000001477,1000002885,1000003571,1000003825,1000004547,1000004760,1000005677,1000006027,1000008156,1000009147,1000009965,1000011061,1000012762,1000013790,1000014169,1000014316,1000014360,1000015031,1000020001,1000022092,1000022745,1000024491,1000025075,1000027831,1000027982,1000029629,1000029693,1000029734,1000030655,1000030899,1000033402,1000033775,1000033894,1000035705,1000036411,1000036799,1000036988,1000037953,1000038647,1000039943,1000044499,1000044517,1000045266,1000045939,1000046692,1000047842,1000049751,1000050689,1000053269,1000055299,1000057100,1000057508,1000061840,1000068871,1000075225,1000075580,1000075613,1000077787,1000078331,1000078373,1000079732,1000080721,1000080738,1000340028,1011176481,1011195020}'::bigint[]))
5.          

CTE rankdates

6. 20.187 57.987 ↓ 4.2 7,934 1

WindowAgg (cost=3,870.41..3,908.11 rows=1,885 width=78) (actual time=35.745..57.987 rows=7,934 loops=1)

7. 7.585 37.800 ↓ 4.2 7,934 1

Sort (cost=3,870.41..3,875.12 rows=1,885 width=46) (actual time=35.733..37.800 rows=7,934 loops=1)

  • Sort Key: i.estpermid, i.perindex
  • Sort Method: quicksort Memory: 812kB
8. 3.469 30.215 ↓ 4.2 7,934 1

Nested Loop (cost=0.56..3,767.86 rows=1,885 width=46) (actual time=0.054..30.215 rows=7,934 loops=1)

9. 0.394 0.394 ↑ 1.1 61 1

CTE Scan on map map_2 (cost=0.00..1.34 rows=67 width=8) (actual time=0.011..0.394 rows=61 loops=1)

10. 26.352 26.352 ↓ 4.6 130 61

Index Scan using treperindex_periodicity_pertype_perlength_estpermid_perendd_idx on treperindex i (cost=0.56..55.94 rows=28 width=46) (actual time=0.087..0.432 rows=130 loops=61)

  • Index Cond: ((periodicity = 3) AND (pertype = 3) AND (perlength = 3) AND (estpermid = map_2.estpermid))
11. 0.135 1,023,729.191 ↑ 1.4 48 1

Sort (cost=8,495.99..8,496.16 rows=67 width=52) (actual time=1,023,729.186..1,023,729.191 rows=48 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: quicksort Memory: 28kB
12. 0.393 1,023,729.056 ↑ 1.4 48 1

Nested Loop (cost=122.73..8,493.96 rows=67 width=52) (actual time=35,148.412..1,023,729.056 rows=48 loops=1)

13. 4.776 4.776 ↑ 1.0 67 1

Index Only Scan using all_equities_vw9_eid_only_idx on all_equities_vw9 q (cost=0.42..297.65 rows=67 width=8) (actual time=4.040..4.776 rows=67 loops=1)

  • Index Cond: (eid = ANY ('{1000001437,1000001477,1000002885,1000003571,1000003825,1000004547,1000004760,1000005677,1000006027,1000008156,1000009147,1000009965,1000011061,1000012762,1000013790,1000014169,1000014316,1000014360,1000015031,1000020001,1000022092,1000022745,1000024491,1000025075,1000027831,1000027982,1000029629,1000029693,1000029734,1000030655,1000030899,1000033402,1000033775,1000033894,1000035705,1000036411,1000036799,1000036988,1000037953,1000038647,1000039943,1000044499,1000044517,1000045266,1000045939,1000046692,1000047842,1000049751,1000050689,1000053269,1000055299,1000057100,1000057508,1000061840,1000068871,1000075225,1000075580,1000075613,1000077787,1000078331,1000078373,1000079732,1000080721,1000080738,1000340028,1011176481,1011195020}'::bigint[]))
  • Heap Fetches: 0
14. 0.268 1,023,723.887 ↑ 1.0 1 67

Limit (cost=122.30..122.31 rows=1 width=72) (actual time=15,279.458..15,279.461 rows=1 loops=67)

15. 0.268 1,023,723.619 ↑ 1.0 1 67

Unique (cost=122.30..122.31 rows=1 width=72) (actual time=15,279.457..15,279.457 rows=1 loops=67)

16. 123.079 1,023,723.351 ↑ 1.0 1 67

Sort (cost=122.30..122.31 rows=1 width=72) (actual time=15,279.453..15,279.453 rows=1 loops=67)

  • Sort Key: ((q1.effectivedate)::date) DESC
  • Sort Method: quicksort Memory: 26kB
17. 303.041 1,023,600.272 ↓ 203.0 203 67

Nested Loop (cost=89.29..122.29 rows=1 width=72) (actual time=14,574.047..15,277.616 rows=203 loops=67)

  • Join Filter: (fiscalspine.estpermid = map.estpermid)
  • Rows Removed by Join Filter: 12200
18. 24,558.850 1,007,780.634 ↓ 13,623.0 13,623 67

Nested Loop (cost=89.29..120.75 rows=1 width=87) (actual time=13,445.971..15,041.502 rows=13,623 loops=67)

  • Join Filter: (((q1.effectivedate)::date >= (fiscalspine.effectivedate)::date) AND (((q1.effectivedate)::date < (fiscalspine.expiredate)::date) OR ((fiscalspine.expiredate)::date IS NULL)))
  • Rows Removed by Join Filter: 61602
19. 5,283.419 906,133.259 ↓ 76,705.0 76,705 67

Hash Join (cost=88.73..112.13 rows=1 width=179) (actual time=13,445.558..13,524.377 rows=76,705 loops=67)

  • Hash Cond: ((c.code)::integer = q1.normcurrpermid)
20. 4.087 7.906 ↑ 1.0 287 67

Bitmap Heap Scan on trecode c (cost=10.50..32.09 rows=287 width=15) (actual time=0.073..0.118 rows=287 loops=67)

  • Recheck Cond: (codetype = 7)
  • Heap Blocks: exact=134
21. 3.819 3.819 ↑ 1.0 287 67

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

  • Index Cond: (codetype = 7)
22. 5,024.665 900,841.934 ↓ 76,705.0 76,705 67

Hash (cost=78.22..78.22 rows=1 width=173) (actual time=13,445.402..13,445.402 rows=76,705 loops=67)

  • Buckets: 32768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 11642kB
23. 5,167.844 895,817.269 ↓ 76,705.0 76,705 67

Nested Loop (cost=0.70..78.22 rows=1 width=173) (actual time=180.171..13,370.407 rows=76,705 loops=67)

24. 787.853 787.853 ↓ 7,934.0 7,934 67

CTE Scan on rankdates p0 (cost=0.00..51.84 rows=1 width=138) (actual time=0.537..11.759 rows=7,934 loops=67)

  • Filter: ((pertype = 3) AND (perlength = 3) AND (periodicity = 3))
25. 889,861.572 889,861.572 ↓ 10.0 10 531,578

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper q1 (cost=0.70..26.37 rows=1 width=95) (actual time=1.354..1.674 rows=10 loops=531,578)

  • Index Cond: ((estpermid = p0.estpermid) AND (measure = 13) AND (pertype = 3) AND ((effectivedate)::text >= '1900-01-01'::text) AND ((effectivedate)::text <= '2019-10-01'::text) AND ((perenddate)::text = (p0.targetperenddate)::text))
  • Filter: (NOT isparent)
26. 77,088.525 77,088.525 ↑ 1.0 1 5,139,235

Index Scan using treperadvance_pkey on treperadvance fiscalspine (cost=0.56..8.58 rows=1 width=98) (actual time=0.015..0.015 rows=1 loops=5,139,235)

  • Index Cond: (((perenddate)::text = (p0.currperenddate)::text) AND (estpermid = p0.estpermid) AND (periodicity = 3) AND (pertype = 3) AND (perlength = 3))
27. 15,516.597 15,516.597 ↑ 1.0 1 912,741

CTE Scan on map (cost=0.00..1.51 rows=1 width=8) (actual time=0.009..0.017 rows=1 loops=912,741)

  • Filter: (eid = q.eid)
  • Rows Removed by Filter: 60
Planning time : 9.566 ms
Execution time : 1,023,731.203 ms