Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE ordered_subscriptions (
  user_id integer,
  start_date date,
  end_date date,
  subscription_type varchar(7)
);

INSERT INTO ordered_subscriptions
  (user_id, start_date, end_date, subscription_type)
VALUES
  (675, '2023-01-01', '2023-05-10', 'Premium'),
  (675, '2023-02-15', '2023-02-28', 'Books'),
  (675, '2023-04-18', '2023-06-18', 'Books'),
  (677, '2023-01-01', '2023-07-10', 'Premium'),
  (677, '2023-02-15', '2023-02-28', 'Books'),
  (677, '2023-04-18', '2023-06-18', 'Books'),
  (699, '2023-01-01', '2023-07-10', 'Premium'),
  (699, '2023-02-15', '2023-02-28', 'Books'),
  (699, '2023-04-18', '2023-06-18', 'Books'),
  (699, '2023-06-20', '2023-06-22', 'Books'),
  (726, '2023-01-01', '2023-10-10', 'Premium'),
  (726, '2023-03-16', '2023-05-28', 'Books'),
  (855, '2023-04-05', '2023-05-28', 'Books'),
  (855, '2023-04-20', '2023-07-25', 'Premium'),
  (900, '2023-07-01', '2023-07-10', 'Premium'),
  (900, '2023-01-01', '2023-12-31', 'Books');

CREATE TABLE



INSERT 0 16


explain analyze with data as (
    select
        s.user_id, s.subscription_type, s.start_date, s.end_date,
        b.start_date as b_start_date, b.end_date as b_end_date,
        case when b.subscription_type is not null
             then lag(b.end_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as pb_end_date,
        case when b.subscription_type is not null
             then lead(b.start_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as nb_start_date,
        case when s.start_date < b.start_date then 'p'
             when s.start_date > b.start_date then 'b' end ||
        case when s.end_date   < b.end_date   then 'b'
             when s.end_date   > b.end_date   then 'p' end as overlap
    from ordered_subscriptions s left outer join ordered_subscriptions as b
        on      s.subscription_type = 'Premium' and b.subscription_type = 'Books'
            and s.user_id = b.user_id
            and s.start_date < b.end_date and s.end_date > b.start_date
), data2 as (
    select
        user_id, subscription_type, overlap,
        case when pb_end_date is not null then 'Y' else 'N' end as linked,
        least(start_date, b_start_date) as d1,
        greatest(start_date, b_start_date) as d2,
        least(end_date, b_end_date) as d3,
        coalesce(nb_start_date, greatest(end_date, b_end_date)) as d4,
        seg_num
    from data left outer join lateral (
            select * from (values (1), (2)) as v(seg_num)
            where subscription_type = 'Premium' and (seg_num = 1 or overlap = 'pp')
        ) as s(seg_num) on 1 = 1
    where (subscription_type = 'Books' or overlap <> 'bb')
)
select
    user_id, subscription_type,
    case when seg_num is null then d1
         when seg_num = 1 then case when overlap = 'bp' then d3 else d1 end
         when seg_num = 2 then d3 end as start_date,
    case when seg_num is null then d3
         when seg_num = 1 then case when overlap = 'bp' then d4 else d2 end
         when seg_num = 2 then d4 end as end_date
from data2
where (linked = 'N' or seg_num = 2)
order by user_id, start_date


QUERY PLAN


Sort  (cost=262.95..262.97 rows=11 width=44) (actual time=0.237..0.240 rows=22 loops=1)

  Sort Key: data2.user_id, (CASE WHEN (data2.seg_num IS NULL) THEN data2.d1 WHEN (data2.seg_num = 1) THEN CASE WHEN (data2.overlap = 'bp'::text) THEN data2.d3 ELSE data2.d1 END WHEN (data2.seg_num = 2) THEN data2.d3 ELSE NULL::date END)

  Sort Method: quicksort  Memory: 26kB

  CTE data

    ->  WindowAgg  (cost=107.64..150.01 rows=1130 width=92) (actual time=0.098..0.124 rows=20 loops=1)

          ->  Sort  (cost=107.64..110.46 rows=1130 width=84) (actual time=0.079..0.082 rows=20 loops=1)

                Sort Key: s.user_id, s.start_date, b.start_date

                Sort Method: quicksort  Memory: 26kB

                ->  Hash Left Join  (cost=24.20..50.33 rows=1130 width=84) (actual time=0.049..0.060 rows=20 loops=1)

                      Hash Cond: (s.user_id = b.user_id)

                      Join Filter: (((s.subscription_type)::text = 'Premium'::text) AND (s.start_date < b.end_date) AND (s.end_date > b.start_date))

                      Rows Removed by Join Filter: 20

                      ->  Seq Scan on ordered_subscriptions s  (cost=0.00..21.30 rows=1130 width=44) (actual time=0.008..0.010 rows=16 loops=1)

                      ->  Hash  (cost=24.12..24.12 rows=6 width=44) (actual time=0.025..0.025 rows=10 loops=1)

                            Buckets: 1024  Batches: 1  Memory Usage: 9kB

                            ->  Seq Scan on ordered_subscriptions b  (cost=0.00..24.12 rows=6 width=44) (actual time=0.007..0.011 rows=10 loops=1)

                                  Filter: ((subscription_type)::text = 'Books'::text)

                                  Rows Removed by Filter: 6

  CTE data2

    ->  Nested Loop Left Join  (cost=0.00..84.48 rows=1124 width=120) (actual time=0.112..0.169 rows=26 loops=1)

          Join Filter: (((data.subscription_type)::text = 'Premium'::text) AND (("*VALUES*".column1 = 1) OR (data.overlap = 'pp'::text)))

          Rows Removed by Join Filter: 22

          ->  CTE Scan on data  (cost=0.00..28.25 rows=1124 width=92) (actual time=0.100..0.137 rows=19 loops=1)

                Filter: (((subscription_type)::text = 'Books'::text) OR (overlap <> 'bb'::text))

                Rows Removed by Filter: 1

          ->  Materialize  (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.001 rows=2 loops=19)

                ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.004..0.005 rows=2 loops=1)

  ->  CTE Scan on data2  (cost=0.00..28.27 rows=11 width=44) (actual time=0.115..0.194 rows=22 loops=1)

        Filter: ((linked = 'N'::text) OR (seg_num = 2))

        Rows Removed by Filter: 4

Planning time: 0.764 ms

Execution time: 0.479 ms



EXPLAIN


explain analyze
with data as (
    select
        s.user_id, s.subscription_type, s.start_date, s.end_date,
        b.start_date as b_start_date, b.end_date as b_end_date,
        lag(b.end_date)    over (partition by s.user_id order by b.start_date) as pb_end_date,
        lead(b.start_date) over (partition by s.user_id order by b.start_date) as nb_start_date,
        case when s.start_date < b.start_date then 'p'
             when s.start_date > b.start_date then 'b' end ||
        case when s.end_date   < b.end_date   then 'b'
             when s.end_date   > b.end_date   then 'p' end as overlap
    from ordered_subscriptions s inner join ordered_subscriptions as b
        on      b.subscription_type = 'Books'
            and s.user_id = b.user_id
            and s.start_date < b.end_date and s.end_date > b.start_date
    where s.subscription_type = 'Premium'
), data2 as (
    select
        user_id, subscription_type, overlap,
        case when pb_end_date is not null then 'Y' else 'N' end as linked,
        least(start_date, b_start_date) as d1,
        greatest(start_date, b_start_date) as d2,
        least(end_date, b_end_date) as d3,
        coalesce(nb_start_date, greatest(end_date, b_end_date)) as d4,
        seg_num
    from data cross join (values (1), (2)) as s(seg_num)
    where (seg_num = 1 or overlap = 'pp') and overlap <> 'bb'
)
select
    user_id, subscription_type,
    case when seg_num = 1 then case when overlap = 'bp' then d3 else d1 end
         when seg_num = 2 then d3 end as start_date,
    case when seg_num = 1 then case when overlap = 'bp' then d4 else d2 end
         when seg_num = 2 then d4 end as end_date
from data2
where (linked = 'N' or seg_num = 2)
union all
select user_id, subscription_type, start_date, end_date
from ordered_subscriptions
where subscription_type = 'Books'
order by user_id, start_date


QUERY PLAN


Sort  (cost=72.91..72.93 rows=8 width=44) (actual time=0.161..0.163 rows=22 loops=1)

  Sort Key: data2.user_id, (CASE WHEN (data2.seg_num = 1) THEN CASE WHEN (data2.overlap = 'bp'::text) THEN data2.d3 ELSE data2.d1 END WHEN (data2.seg_num = 2) THEN data2.d3 ELSE NULL::date END)

  Sort Method: quicksort  Memory: 26kB

  CTE data

    ->  WindowAgg  (cost=48.37..48.41 rows=1 width=92) (actual time=0.076..0.089 rows=10 loops=1)

          ->  Sort  (cost=48.37..48.38 rows=1 width=52) (actual time=0.063..0.065 rows=10 loops=1)

                Sort Key: s.user_id, b.start_date

                Sort Method: quicksort  Memory: 25kB

                ->  Hash Join  (cost=24.20..48.36 rows=1 width=52) (actual time=0.043..0.049 rows=10 loops=1)

                      Hash Cond: (s.user_id = b.user_id)

                      Join Filter: ((s.start_date < b.end_date) AND (s.end_date > b.start_date))

                      ->  Seq Scan on ordered_subscriptions s  (cost=0.00..24.12 rows=6 width=44) (actual time=0.008..0.011 rows=6 loops=1)

                            Filter: ((subscription_type)::text = 'Premium'::text)

                            Rows Removed by Filter: 10

                      ->  Hash  (cost=24.12..24.12 rows=6 width=12) (actual time=0.020..0.020 rows=10 loops=1)

                            Buckets: 1024  Batches: 1  Memory Usage: 9kB

                            ->  Seq Scan on ordered_subscriptions b  (cost=0.00..24.12 rows=6 width=12) (actual time=0.005..0.008 rows=10 loops=1)

                                  Filter: ((subscription_type)::text = 'Books'::text)

                                  Rows Removed by Filter: 6

  CTE data2

    ->  Nested Loop  (cost=0.00..0.10 rows=2 width=120) (actual time=0.085..0.116 rows=16 loops=1)

          Join Filter: (("*VALUES*".column1 = 1) OR (data.overlap = 'pp'::text))

          Rows Removed by Join Filter: 2

          ->  CTE Scan on data  (cost=0.00..0.02 rows=1 width=92) (actual time=0.078..0.097 rows=9 loops=1)

                Filter: (overlap <> 'bb'::text)

                Rows Removed by Filter: 1

          ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.001 rows=2 loops=9)

  ->  Append  (cost=0.00..24.29 rows=8 width=44) (actual time=0.087..0.140 rows=22 loops=1)

        ->  CTE Scan on data2  (cost=0.00..0.08 rows=2 width=44) (actual time=0.087..0.129 rows=12 loops=1)

              Filter: ((linked = 'N'::text) OR (seg_num = 2))

              Rows Removed by Filter: 4

        ->  Seq Scan on ordered_subscriptions  (cost=0.00..24.12 rows=6 width=44) (actual time=0.007..0.009 rows=10 loops=1)

              Filter: ((subscription_type)::text = 'Books'::text)

              Rows Removed by Filter: 6

Planning time: 0.264 ms

Execution time: 0.376 ms



EXPLAIN