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