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