explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5WT : New query BASELINE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 105.859 218.719 ↑ 11.3 19,800 1

Nested Loop (cost=511.13..39,460.18 rows=223,078 width=165) (actual time=34.680..218.719 rows=19,800 loops=1)

  • Join Filter: ((iss.ixrs_date >= i.interval_start_time) AND (iss.ixrs_date < i.interval_end_time))
  • Rows Removed by Join Filter: 144,416
2.          

CTE interval

3. 0.014 0.014 ↑ 12.5 8 1

Function Scan on t (cost=0.01..1.00 rows=100 width=16) (actual time=0.008..0.014 rows=8 loops=1)

4.          

CTE interval-with-interval-index

5. 0.036 0.081 ↑ 12.5 8 1

WindowAgg (cost=5.32..7.07 rows=100 width=24) (actual time=0.044..0.081 rows=8 loops=1)

6. 0.020 0.045 ↑ 12.5 8 1

Sort (cost=5.32..5.57 rows=100 width=16) (actual time=0.036..0.045 rows=8 loops=1)

  • Sort Key: i_1.interval_start_time DESC
  • Sort Method: quicksort Memory: 25kB
7. 0.025 0.025 ↑ 12.5 8 1

CTE Scan on "interval" i_1 (cost=0.00..2.00 rows=100 width=16) (actual time=0.010..0.025 rows=8 loops=1)

8. 0.100 0.100 ↑ 12.5 8 1

CTE Scan on "interval-with-interval-index" i (cost=0.00..2.00 rows=100 width=24) (actual time=0.046..0.100 rows=8 loops=1)

9. 97.708 112.760 ↓ 1.0 20,527 8

Materialize (cost=503.06..4,365.55 rows=20,077 width=141) (actual time=0.130..14.095 rows=20,527 loops=8)

10. 14.085 15.052 ↓ 1.0 20,527 1

Bitmap Heap Scan on "IxRSSiteSnapshot" iss (cost=503.06..4,265.16 rows=20,077 width=141) (actual time=1.031..15.052 rows=20,527 loops=1)

  • Recheck Cond: (sponsor_trial_id = '0fd77df9-1596-59cf-9943-0e80f3c6e9a0'::uuid)
  • Filter: ((ixrs_date >= '2020-04-27 00:00:00+00'::timestamp with time zone) AND (ixrs_date < '2020-06-22 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1,454
  • Heap Blocks: exact=518
11. 0.967 0.967 ↓ 1.0 21,981 1

Bitmap Index Scan on "IxRSSiteSnapshot_sponsor_trial_id_idx" (cost=0.00..498.04 rows=21,549 width=0) (actual time=0.967..0.967 rows=21,981 loops=1)

  • Index Cond: (sponsor_trial_id = '0fd77df9-1596-59cf-9943-0e80f3c6e9a0'::uuid)
Planning time : 0.254 ms
Execution time : 228.881 ms