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(a,',') 
                 from generate_series(1,1e5)g
                ,lateral(select g,gen_random_uuid()::text a)_),999 ),
(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


vacuum analyze billing_cycle;

VACUUM


select pg_column_compression(something_toasted),
       pg_size_pretty(
           pg_column_size(something_toasted)::bigint) 
from billing_cycle;

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';

select oid::regclass from pg_class where reltoastrelid='billing_cycle'::regclass;


pg_column_compression
pg_size_pretty


null
3613 kB

null
3613 kB



SELECT 2




oid
relname
relkind
relpages
reltuples
reltoastrelid
relkind
relpages
reltuples


16399
billing_cycle
r
1
2
16402
t
927
3708



SELECT 1




oid



SELECT 0


prepare your_query as 
WITH segmented_customer AS (SELECT *
                            FROM customer
                            WHERE integration_id = 162
                              AND (1 = 1 OR 1 = 1)),
     segmented_subscription
         AS (SELECT * FROM subscription WHERE integration_id = 162 AND 1 = 1),
     segmented_billing_cycle
         AS (SELECT * FROM billing_cycle WHERE integration_id = 162),
     segmented_product_version
         AS (SELECT * FROM product_version WHERE integration_id = 162 AND 1 = 1)
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 segmented_customer
         JOIN segmented_subscription
              ON segmented_customer.id = segmented_subscription.customer_id
         JOIN segmented_billing_cycle
              ON segmented_subscription.id = segmented_billing_cycle.subscription_id
         JOIN segmented_product_version
              ON segmented_billing_cycle.id = segmented_product_version.billing_cycle_id;

EXPLAIN (ANALYZE, VERBOSE) execute your_query;
execute your_query;

PREPARE




QUERY PLAN


Nested Loop  (cost=0.00..58.14 rows=1 width=292) (actual time=0.020..0.023 rows=1 loops=1)

  Output: customer.id, customer.name, subscription.override_start_date, subscription.source_start_date, subscription.override_end_date, subscription.computed_end_date, product_version.override_mrr_amount, product_version.total_mrr, product_version.version_start_date, product_version.version_end_date

  Join Filter: ((customer.id = subscription.customer_id) AND (subscription.id = billing_cycle.subscription_id))

  ->  Nested Loop  (cost=0.00..42.11 rows=1 width=168) (actual time=0.016..0.018 rows=1 loops=1)

        Output: customer.id, customer.name, billing_cycle.subscription_id, product_version.override_mrr_amount, product_version.total_mrr, product_version.version_start_date, product_version.version_end_date

        ->  Nested Loop  (cost=0.00..17.05 rows=1 width=132) (actual time=0.013..0.014 rows=1 loops=1)

              Output: billing_cycle.subscription_id, product_version.override_mrr_amount, product_version.total_mrr, product_version.version_start_date, product_version.version_end_date

              Join Filter: (billing_cycle.id = product_version.billing_cycle_id)

              ->  Seq Scan on public.billing_cycle  (cost=0.00..1.02 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

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

                    Filter: (billing_cycle.integration_id = 162)

                    Rows Removed by Filter: 1

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

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

                    Filter: (product_version.integration_id = 162)

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

              Output: customer.id, customer.name, customer.integration_id

              Filter: (customer.integration_id = 162)

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

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

        Filter: (subscription.integration_id = 162)

Planning Time: 0.540 ms

Execution Time: 0.047 ms



EXPLAIN




customer_id
customer_name
sub_override_start_ts
sub_source_start_ts
sub_override_end_ts
sub_computed_end_ts
override_mrr_amount
total_mrr
version_start_ts
version_end_ts


1
segmented_customer_name1
override_start_date
source_start_date
override_end_date
computed_end_date
override_mrr_amount
total_mrr
version_start_date
version_end_date



SELECT 1


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;
execute your_query_without_cte;

PREPARE




QUERY PLAN


Nested Loop  (cost=0.00..58.14 rows=1 width=292) (actual time=0.016..0.018 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..42.11 rows=1 width=168) (actual time=0.012..0.014 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..17.05 rows=1 width=132) (actual time=0.010..0.011 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)

              ->  Seq Scan on public.billing_cycle segmented_billing_cycle  (cost=0.00..1.02 rows=1 width=8) (actual time=0.006..0.007 rows=1 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: 1

              ->  Seq Scan on public.product_version segmented_product_version  (cost=0.00..16.00 rows=2 width=132) (actual time=0.002..0.002 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)

        ->  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.230 ms

Execution Time: 0.039 ms



EXPLAIN




customer_id
customer_name
sub_override_start_ts
sub_source_start_ts
sub_override_end_ts
sub_computed_end_ts
override_mrr_amount
total_mrr
version_start_ts
version_end_ts


1
segmented_customer_name1
override_start_date
source_start_date
override_end_date
computed_end_date
override_mrr_amount
total_mrr
version_start_date
version_end_date



SELECT 1


insert into billing_cycle 
select random()*100, 
       random()*100,
       random()*100,
       random(),
       random()*1000
from generate_series(1,2e4);

INSERT 0 20000


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;
execute your_query_without_cte;

DEALLOCATE



PREPARE




QUERY PLAN


Nested Loop  (cost=0.29..65.84 rows=1 width=292) (actual time=0.034..0.037 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

  ->  Nested Loop  (cost=0.00..41.19 rows=1 width=168) (actual time=0.018..0.019 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_subscription.id

        Join Filter: (segmented_customer.id = segmented_subscription.customer_id)

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

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

              Filter: (segmented_customer.integration_id = 162)

        ->  Materialize  (cost=0.00..16.01 rows=2 width=136) (actual time=0.006..0.007 rows=1 loops=1)

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

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

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

                    Filter: (segmented_subscription.integration_id = 162)

  ->  Nested Loop  (cost=0.29..24.64 rows=1 width=132) (actual time=0.013..0.015 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.002..0.003 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.29..4.31 rows=1 width=8) (actual time=0.009..0.010 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.subscription_id = segmented_subscription.id) AND (segmented_billing_cycle.integration_id = 162))

              Heap Fetches: 0

Planning Time: 0.589 ms

Execution Time: 0.083 ms



EXPLAIN




customer_id
customer_name
sub_override_start_ts
sub_source_start_ts
sub_override_end_ts
sub_computed_end_ts
override_mrr_amount
total_mrr
version_start_ts
version_end_ts


1
segmented_customer_name1
override_start_date
source_start_date
override_end_date
computed_end_date
override_mrr_amount
total_mrr
version_start_date
version_end_date



SELECT 1