explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A9ys

Settings
# exclusive inclusive rows x rows loops node
1. 13.027 186,529.553 ↓ 9,137.8 82,240 1

Unique (cost=316,557.08..316,557.15 rows=9 width=52) (actual time=186,505.284..186,529.553 rows=82,240 loops=1)

2. 78.161 186,516.526 ↓ 9,137.8 82,240 1

Sort (cost=316,557.08..316,557.10 rows=9 width=52) (actual time=186,505.282..186,516.526 rows=82,240 loops=1)

  • Sort Key: q.eid, ((q1.effectivedate)::date)
  • Sort Method: external sort Disk: 3536kB
3. 62.102 186,438.365 ↓ 9,137.8 82,240 1

Nested Loop (cost=35,083.11..316,556.94 rows=9 width=52) (actual time=870.224..186,438.365 rows=82,240 loops=1)

4. 0.215 1.316 ↓ 6.8 61 1

Nested Loop (cost=0.72..815.09 rows=9 width=16) (actual time=0.020..1.316 rows=61 loops=1)

5. 0.565 0.565 ↑ 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=0.013..0.565 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
6. 0.536 0.536 ↑ 1.0 1 67

Index Scan using ibes2mapping_vw1_eid_idx on ibes2mapping_vw1 map (cost=0.29..7.71 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=67)

  • Index Cond: (eid = q.eid)
7. 24.156 186,374.947 ↓ 1,348.0 1,348 61

Unique (cost=35,082.40..35,082.40 rows=1 width=72) (actual time=3,054.708..3,055.327 rows=1,348 loops=61)

8. 303.414 186,350.791 ↓ 2,461.0 2,461 61

Sort (cost=35,082.40..35,082.40 rows=1 width=72) (actual time=3,054.696..3,054.931 rows=2,461 loops=61)

  • Sort Key: ((q1.effectivedate)::date), q1.effectivedate DESC
  • Sort Method: quicksort Memory: 42kB
9. 10,086.167 186,047.377 ↓ 2,461.0 2,461 61

Nested Loop (cost=34.68..35,082.39 rows=1 width=72) (actual time=1.288..3,049.957 rows=2,461 loops=61)

  • Join Filter: (q1.normcurrpermid = (c.code)::integer)
  • Rows Removed by Join Filter: 703719
10. 1,582.727 166,805.476 ↓ 2,461.0 2,461 61

Nested Loop (cost=24.18..35,045.25 rows=1 width=57) (actual time=1.242..2,734.516 rows=2,461 loops=61)

  • Join Filter: (((q1.effectivedate)::text >= (ad.effectivedate)::text) AND (((q1.effectivedate)::text < (ad.expiredate)::text) OR (ad.expiredate IS NULL)))
  • Rows Removed by Join Filter: 7628
11. 778.148 159,517.928 ↓ 346.4 10,391 61

Nested Loop (cost=23.62..34,787.33 rows=30 width=99) (actual time=0.274..2,615.048 rows=10,391 loops=61)

12. 906.399 906.399 ↓ 8.2 10,391 61

Index Scan using tresumper_estpermid_measure_pertype_effectivedate_perenddat_idx on tresumper q1 (cost=0.70..5,265.72 rows=1,271 width=95) (actual time=0.048..14.859 rows=10,391 loops=61)

  • Index Cond: ((map.estpermid = estpermid) AND (measure = 9) AND (pertype = 3) AND ((effectivedate)::text >= '1990-10-02'::text) AND ((effectivedate)::text <= '2020-01-01'::text))
  • Filter: (NOT isparent)
13. 10,775.773 157,833.381 ↑ 1.0 1 633,869

Subquery Scan on p0 (cost=22.92..23.22 rows=1 width=74) (actual time=0.213..0.249 rows=1 loops=633,869)

  • Filter: ((q1.perenddate)::text = (p0.targetperenddate)::text)
  • Rows Removed by Filter: 148
14. 50,709.520 147,057.608 ↓ 16.6 149 633,869

WindowAgg (cost=22.92..23.10 rows=9 width=78) (actual time=0.142..0.232 rows=149 loops=633,869)

15. 25,988.629 96,348.088 ↓ 16.6 149 633,869

Sort (cost=22.92..22.95 rows=9 width=46) (actual time=0.139..0.152 rows=149 loops=633,869)

  • Sort Key: i.perindex
  • Sort Method: quicksort Memory: 26kB
16. 70,359.459 70,359.459 ↓ 16.6 149 633,869

Index Scan using treperindex_periodicity_pertype_perlength_estpermid_perendd_idx on treperindex i (cost=0.56..22.78 rows=9 width=46) (actual time=0.013..0.111 rows=149 loops=633,869)

  • Index Cond: ((periodicity = 3) AND (pertype = q1.pertype) AND (perlength = 3) AND (estpermid = q1.estpermid))
17. 5,704.821 5,704.821 ↑ 1.0 1 633,869

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=633,869)

  • Index Cond: (((perenddate)::text = (p0.currperenddate)::text) AND (estpermid = p0.estpermid) AND (periodicity = p0.periodicity) AND (pertype = p0.pertype) AND (perlength = p0.perlength))
18. 4,502.820 9,155.734 ↑ 1.0 287 150,094

Bitmap Heap Scan on trecode c (cost=10.50..32.09 rows=287 width=15) (actual time=0.038..0.061 rows=287 loops=150,094)

  • Recheck Cond: (codetype = 7)
  • Heap Blocks: exact=300188
19. 4,652.914 4,652.914 ↑ 1.0 287 150,094

Bitmap Index Scan on trecode_pkey (cost=0.00..10.43 rows=287 width=0) (actual time=0.031..0.031 rows=287 loops=150,094)

  • Index Cond: (codetype = 7)
Planning time : 3.922 ms
Execution time : 186,534.426 ms