Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table customer(
  id,name,integration_id
) as values 
(1,'segmented_customer_name1',162);

create table subscription(
  id,customer_id,integration_id,override_start_date,source_start_date,override_end_date,computed_end_date
) as values
(1,1,162,'override_start_date','source_start_date','override_end_date','computed_end_date');

create table product_version(
  id,billing_cycle_id,integration_id,
  override_mrr_amount,total_mrr,version_start_date,version_end_date
) as values
(1,1,162,'override_mrr_amount','total_mrr','version_start_date','version_end_date');

create table billing_cycle(
  id int,subscription_id int,integration_id int,something_toasted text,organization_id int
);
alter table billing_cycle alter column something_toasted set storage external;
insert into billing_cycle values
(1,1,162,(select string_agg(repeat(a,10),',') 
         from generate_series(1,1e2)g
        ,lateral(select g,gen_random_uuid()::text a)_),888),
(2,2,163,(select string_agg(a,',') 
                 from generate_series(1,1e5)g
                ,lateral(select g,gen_random_uuid()::text a)_),999 );

SELECT 1

SELECT 1

SELECT 1

CREATE TABLE

ALTER TABLE

INSERT 0 2

select setseed(.42);
insert into billing_cycle 
select random()*200, 
       random()*200,
       random()*200,
       (select string_agg(repeat(random_text,13),',') 
        from generate_series(1,20)g
        ,lateral(select g,gen_random_uuid()::text random_text)_),
       random()*3000
from generate_series(1,1e3);

setseed

SELECT 1

INSERT 0 1000

vacuum analyze billing_cycle;

VACUUM

select pg_column_compression(something_toasted),
       pg_size_pretty(
           pg_column_size(something_toasted)::bigint) 
from billing_cycle tablesample bernoulli(.42)repeatable(.42)limit 10;

select t1.oid, t1.relname, t1.relkind, t1.relpages, t1.reltuples, 
       t1.reltoastrelid, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'
  and t2.relkind = 't'
  and t1.relname='billing_cycle';

pg_column_compression
pg_size_pretty

null
9379 bytes

null
9379 bytes

null
9379 bytes

SELECT 3

oid
relname
relkind
relpages
reltuples
reltoastrelid
relkind
relpages
reltuples

16399
billing_cycle
r
9
1002
16402
t
1718
6873

SELECT 1

--deallocate your_query_without_cte;
prepare your_query_without_cte as 
SELECT segmented_customer.id                         AS customer_id,
       segmented_customer.name                       AS customer_name,
       segmented_subscription.override_start_date    AS sub_override_start_ts,
       segmented_subscription.source_start_date      AS sub_source_start_ts,
       segmented_subscription.override_end_date      AS sub_override_end_ts,
       segmented_subscription.computed_end_date      AS sub_computed_end_ts,
       segmented_product_version.override_mrr_amount AS override_mrr_amount,
       segmented_product_version.total_mrr           AS total_mrr,
       segmented_product_version.version_start_date  AS version_start_ts,
       segmented_product_version.version_end_date    AS version_end_ts
FROM customer segmented_customer
         JOIN subscription segmented_subscription
              ON segmented_customer.id = segmented_subscription.customer_id
             AND segmented_subscription.integration_id = 162
             AND segmented_customer.integration_id = 162
         JOIN billing_cycle segmented_billing_cycle
              ON segmented_subscription.id = segmented_billing_cycle.subscription_id
             AND segmented_billing_cycle.integration_id = 162
         JOIN product_version segmented_product_version
              ON segmented_billing_cycle.id = segmented_product_version.billing_cycle_id
             AND segmented_product_version.integration_id = 162;

EXPLAIN (ANALYZE, VERBOSE) execute your_query_without_cte;

PREPARE

QUERY PLAN

Nested Loop  (cost=0.00..78.80 rows=1 width=292) (actual time=0.025..0.119 rows=1 loops=1)

  Output: segmented_customer.id, segmented_customer.name, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

  Join Filter: ((segmented_customer.id = segmented_subscription.customer_id) AND (segmented_subscription.id = segmented_billing_cycle.subscription_id))

  ->  Nested Loop  (cost=0.00..62.77 rows=1 width=168) (actual time=0.021..0.115 rows=1 loops=1)

        Output: segmented_customer.id, segmented_customer.name, segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

        ->  Nested Loop  (cost=0.00..37.71 rows=1 width=132) (actual time=0.018..0.111 rows=1 loops=1)

              Output: segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

              Join Filter: (segmented_billing_cycle.id = segmented_product_version.billing_cycle_id)

              Rows Removed by Join Filter: 5

              ->  Seq Scan on public.billing_cycle segmented_billing_cycle  (cost=0.00..21.52 rows=6 width=8) (actual time=0.008..0.097 rows=6 loops=1)

                    Output: segmented_billing_cycle.id, segmented_billing_cycle.subscription_id, segmented_billing_cycle.integration_id, segmented_billing_cycle.something_toasted, segmented_billing_cycle.organization_id

                    Filter: (segmented_billing_cycle.integration_id = 162)

                    Rows Removed by Filter: 996

              ->  Materialize  (cost=0.00..16.01 rows=2 width=132) (actual time=0.001..0.002 rows=1 loops=6)

                    Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                    ->  Seq Scan on public.product_version segmented_product_version  (cost=0.00..16.00 rows=2 width=132) (actual time=0.005..0.005 rows=1 loops=1)

                          Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                          Filter: (segmented_product_version.integration_id = 162)

        ->  Seq Scan on public.customer segmented_customer  (cost=0.00..25.00 rows=6 width=36) (actual time=0.003..0.003 rows=1 loops=1)

              Output: segmented_customer.id, segmented_customer.name, segmented_customer.integration_id

              Filter: (segmented_customer.integration_id = 162)

  ->  Seq Scan on public.subscription segmented_subscription  (cost=0.00..16.00 rows=2 width=136) (actual time=0.002..0.003 rows=1 loops=1)

        Output: segmented_subscription.id, segmented_subscription.customer_id, segmented_subscription.integration_id, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date

        Filter: (segmented_subscription.integration_id = 162)

Planning Time: 0.448 ms

Execution Time: 0.149 ms

EXPLAIN

update billing_cycle set something_toasted=null;

UPDATE 1002

vacuum analyze billing_cycle;

VACUUM

deallocate your_query_without_cte;
prepare your_query_without_cte as 
SELECT segmented_customer.id                         AS customer_id,
       segmented_customer.name                       AS customer_name,
       segmented_subscription.override_start_date    AS sub_override_start_ts,
       segmented_subscription.source_start_date      AS sub_source_start_ts,
       segmented_subscription.override_end_date      AS sub_override_end_ts,
       segmented_subscription.computed_end_date      AS sub_computed_end_ts,
       segmented_product_version.override_mrr_amount AS override_mrr_amount,
       segmented_product_version.total_mrr           AS total_mrr,
       segmented_product_version.version_start_date  AS version_start_ts,
       segmented_product_version.version_end_date    AS version_end_ts
FROM customer segmented_customer
         JOIN subscription segmented_subscription
              ON segmented_customer.id = segmented_subscription.customer_id
             AND segmented_subscription.integration_id = 162
             AND segmented_customer.integration_id = 162
         JOIN billing_cycle segmented_billing_cycle
              ON segmented_subscription.id = segmented_billing_cycle.subscription_id
             AND segmented_billing_cycle.integration_id = 162
         JOIN product_version segmented_product_version
              ON segmented_billing_cycle.id = segmented_product_version.billing_cycle_id
             AND segmented_product_version.integration_id = 162;

EXPLAIN (ANALYZE, VERBOSE) execute your_query_without_cte;

DEALLOCATE

PREPARE

QUERY PLAN

Nested Loop  (cost=0.00..83.80 rows=1 width=292) (actual time=0.028..0.119 rows=1 loops=1)

  Output: segmented_customer.id, segmented_customer.name, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

  Join Filter: ((segmented_customer.id = segmented_subscription.customer_id) AND (segmented_subscription.id = segmented_billing_cycle.subscription_id))

  ->  Nested Loop  (cost=0.00..67.77 rows=1 width=168) (actual time=0.024..0.114 rows=1 loops=1)

        Output: segmented_customer.id, segmented_customer.name, segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

        ->  Nested Loop  (cost=0.00..42.71 rows=1 width=132) (actual time=0.021..0.110 rows=1 loops=1)

              Output: segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

              Join Filter: (segmented_billing_cycle.id = segmented_product_version.billing_cycle_id)

              Rows Removed by Join Filter: 5

              ->  Seq Scan on public.billing_cycle segmented_billing_cycle  (cost=0.00..26.52 rows=6 width=8) (actual time=0.013..0.097 rows=6 loops=1)

                    Output: segmented_billing_cycle.id, segmented_billing_cycle.subscription_id, segmented_billing_cycle.integration_id, segmented_billing_cycle.something_toasted, segmented_billing_cycle.organization_id

                    Filter: (segmented_billing_cycle.integration_id = 162)

                    Rows Removed by Filter: 996

              ->  Materialize  (cost=0.00..16.01 rows=2 width=132) (actual time=0.001..0.001 rows=1 loops=6)

                    Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                    ->  Seq Scan on public.product_version segmented_product_version  (cost=0.00..16.00 rows=2 width=132) (actual time=0.003..0.003 rows=1 loops=1)

                          Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                          Filter: (segmented_product_version.integration_id = 162)

        ->  Seq Scan on public.customer segmented_customer  (cost=0.00..25.00 rows=6 width=36) (actual time=0.002..0.003 rows=1 loops=1)

              Output: segmented_customer.id, segmented_customer.name, segmented_customer.integration_id

              Filter: (segmented_customer.integration_id = 162)

  ->  Seq Scan on public.subscription segmented_subscription  (cost=0.00..16.00 rows=2 width=136) (actual time=0.002..0.002 rows=1 loops=1)

        Output: segmented_subscription.id, segmented_subscription.customer_id, segmented_subscription.integration_id, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date

        Filter: (segmented_subscription.integration_id = 162)

Planning Time: 0.350 ms

Execution Time: 0.149 ms

EXPLAIN

select setseed(.42);
delete from billing_cycle where id>2;
insert into billing_cycle 
select random()*200, 
       random()*200,
       random()*200,
       (select string_agg(repeat(random_text,11),',') 
        from generate_series(1,20)g
        ,lateral(select g,gen_random_uuid()::text random_text)_),
       random()*3000
from generate_series(1,1e3);

setseed

SELECT 1

DELETE 986

INSERT 0 1000

vacuum analyze billing_cycle;

VACUUM

deallocate your_query_without_cte;
prepare your_query_without_cte as 
SELECT segmented_customer.id                         AS customer_id,
       segmented_customer.name                       AS customer_name,
       segmented_subscription.override_start_date    AS sub_override_start_ts,
       segmented_subscription.source_start_date      AS sub_source_start_ts,
       segmented_subscription.override_end_date      AS sub_override_end_ts,
       segmented_subscription.computed_end_date      AS sub_computed_end_ts,
       segmented_product_version.override_mrr_amount AS override_mrr_amount,
       segmented_product_version.total_mrr           AS total_mrr,
       segmented_product_version.version_start_date  AS version_start_ts,
       segmented_product_version.version_end_date    AS version_end_ts
FROM customer segmented_customer
         JOIN subscription segmented_subscription
              ON segmented_customer.id = segmented_subscription.customer_id
             AND segmented_subscription.integration_id = 162
             AND segmented_customer.integration_id = 162
         JOIN billing_cycle segmented_billing_cycle
              ON segmented_subscription.id = segmented_billing_cycle.subscription_id
             AND segmented_billing_cycle.integration_id = 162
         JOIN product_version segmented_product_version
              ON segmented_billing_cycle.id = segmented_product_version.billing_cycle_id
             AND segmented_product_version.integration_id = 162;

EXPLAIN (ANALYZE, VERBOSE) execute your_query_without_cte;

DEALLOCATE

PREPARE

QUERY PLAN

Nested Loop  (cost=0.00..83.98 rows=1 width=292) (actual time=0.107..0.114 rows=1 loops=1)

  Output: segmented_customer.id, segmented_customer.name, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

  Join Filter: ((segmented_customer.id = segmented_subscription.customer_id) AND (segmented_subscription.id = segmented_billing_cycle.subscription_id))

  ->  Nested Loop  (cost=0.00..67.95 rows=1 width=168) (actual time=0.104..0.109 rows=1 loops=1)

        Output: segmented_customer.id, segmented_customer.name, segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

        ->  Nested Loop  (cost=0.00..42.89 rows=1 width=132) (actual time=0.101..0.106 rows=1 loops=1)

              Output: segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

              Join Filter: (segmented_billing_cycle.id = segmented_product_version.billing_cycle_id)

              Rows Removed by Join Filter: 5

              ->  Seq Scan on public.billing_cycle segmented_billing_cycle  (cost=0.00..26.70 rows=6 width=8) (actual time=0.030..0.093 rows=6 loops=1)

                    Output: segmented_billing_cycle.id, segmented_billing_cycle.subscription_id, segmented_billing_cycle.integration_id, segmented_billing_cycle.something_toasted, segmented_billing_cycle.organization_id

                    Filter: (segmented_billing_cycle.integration_id = 162)

                    Rows Removed by Filter: 1010

              ->  Materialize  (cost=0.00..16.01 rows=2 width=132) (actual time=0.001..0.001 rows=1 loops=6)

                    Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                    ->  Seq Scan on public.product_version segmented_product_version  (cost=0.00..16.00 rows=2 width=132) (actual time=0.003..0.003 rows=1 loops=1)

                          Output: segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date, segmented_product_version.billing_cycle_id

                          Filter: (segmented_product_version.integration_id = 162)

        ->  Seq Scan on public.customer segmented_customer  (cost=0.00..25.00 rows=6 width=36) (actual time=0.002..0.002 rows=1 loops=1)

              Output: segmented_customer.id, segmented_customer.name, segmented_customer.integration_id

              Filter: (segmented_customer.integration_id = 162)

  ->  Seq Scan on public.subscription segmented_subscription  (cost=0.00..16.00 rows=2 width=136) (actual time=0.002..0.002 rows=1 loops=1)

        Output: segmented_subscription.id, segmented_subscription.customer_id, segmented_subscription.integration_id, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date

        Filter: (segmented_subscription.integration_id = 162)

Planning Time: 0.312 ms

Execution Time: 0.142 ms

EXPLAIN

create index on billing_cycle(id,subscription_id,integration_id);

CREATE INDEX

vacuum analyze billing_cycle;

VACUUM

deallocate your_query_without_cte;
prepare your_query_without_cte as 
SELECT segmented_customer.id                         AS customer_id,
       segmented_customer.name                       AS customer_name,
       segmented_subscription.override_start_date    AS sub_override_start_ts,
       segmented_subscription.source_start_date      AS sub_source_start_ts,
       segmented_subscription.override_end_date      AS sub_override_end_ts,
       segmented_subscription.computed_end_date      AS sub_computed_end_ts,
       segmented_product_version.override_mrr_amount AS override_mrr_amount,
       segmented_product_version.total_mrr           AS total_mrr,
       segmented_product_version.version_start_date  AS version_start_ts,
       segmented_product_version.version_end_date    AS version_end_ts
FROM customer segmented_customer
         JOIN subscription segmented_subscription
              ON segmented_customer.id = segmented_subscription.customer_id
             AND segmented_subscription.integration_id = 162
             AND segmented_customer.integration_id = 162
         JOIN billing_cycle segmented_billing_cycle
              ON segmented_subscription.id = segmented_billing_cycle.subscription_id
             AND segmented_billing_cycle.integration_id = 162
         JOIN product_version segmented_product_version
              ON segmented_billing_cycle.id = segmented_product_version.billing_cycle_id
             AND segmented_product_version.integration_id = 162;

EXPLAIN (ANALYZE, VERBOSE) execute your_query_without_cte;

DEALLOCATE

PREPARE

QUERY PLAN

Nested Loop  (cost=0.28..65.78 rows=1 width=292) (actual time=0.022..0.025 rows=1 loops=1)

  Output: segmented_customer.id, segmented_customer.name, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

  Join Filter: ((segmented_customer.id = segmented_subscription.customer_id) AND (segmented_billing_cycle.subscription_id = segmented_subscription.id))

  ->  Nested Loop  (cost=0.28..49.75 rows=1 width=168) (actual time=0.019..0.021 rows=1 loops=1)

        Output: segmented_customer.id, segmented_customer.name, segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

        ->  Nested Loop  (cost=0.28..24.69 rows=1 width=132) (actual time=0.016..0.017 rows=1 loops=1)

              Output: segmented_billing_cycle.subscription_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

              ->  Seq Scan on public.product_version segmented_product_version  (cost=0.00..16.00 rows=2 width=132) (actual time=0.006..0.007 rows=1 loops=1)

                    Output: segmented_product_version.id, segmented_product_version.billing_cycle_id, segmented_product_version.integration_id, segmented_product_version.override_mrr_amount, segmented_product_version.total_mrr, segmented_product_version.version_start_date, segmented_product_version.version_end_date

                    Filter: (segmented_product_version.integration_id = 162)

              ->  Index Only Scan using billing_cycle_id_subscription_id_integration_id_idx on public.billing_cycle segmented_billing_cycle  (cost=0.28..4.33 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

                    Output: segmented_billing_cycle.id, segmented_billing_cycle.subscription_id, segmented_billing_cycle.integration_id

                    Index Cond: ((segmented_billing_cycle.id = segmented_product_version.billing_cycle_id) AND (segmented_billing_cycle.integration_id = 162))

                    Heap Fetches: 0

        ->  Seq Scan on public.customer segmented_customer  (cost=0.00..25.00 rows=6 width=36) (actual time=0.002..0.003 rows=1 loops=1)

              Output: segmented_customer.id, segmented_customer.name, segmented_customer.integration_id

              Filter: (segmented_customer.integration_id = 162)

  ->  Seq Scan on public.subscription segmented_subscription  (cost=0.00..16.00 rows=2 width=136) (actual time=0.002..0.002 rows=1 loops=1)

        Output: segmented_subscription.id, segmented_subscription.customer_id, segmented_subscription.integration_id, segmented_subscription.override_start_date, segmented_subscription.source_start_date, segmented_subscription.override_end_date, segmented_subscription.computed_end_date

        Filter: (segmented_subscription.integration_id = 162)

Planning Time: 0.372 ms

Execution Time: 0.052 ms

EXPLAIN