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?.
select version();
| version |
|---|
| PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE ad
(
id SERIAL PRIMARY KEY
);
CREATE TABLE
CREATE TABLE _user
(
id UUID PRIMARY KEY
);
CREATE TABLE
create table "impressions" (
id serial primary key,
ip inet,
ad_id integer references ad(id),
_user uuid references _user(id),
created_at timestamp default now()
)
CREATE TABLE
create table "purchases" (
id serial primary key,
ip inet,
ad_id integer references ad(id),
_user uuid references _user(id),
-- ... few other columns
created_at timestamp default now()
)
CREATE TABLE
create table "clicks" (
id serial primary key,
ip inet,
ad_id integer references ad(id),
_user uuid references _user(id),
created_at timestamp default now()
)
CREATE TABLE
WITH cte AS
(
SELECT
_user, COUNT(_user) AS cnt
FROM impressions
GROUP BY _user
UNION ALL
SELECT
_user, COUNT(_user)
FROM purchases
GROUP BY _user
UNION ALL
SELECT
_user, COUNT(_user)
FROM clicks
GROUP BY _user
)
SELECT _user, SUM(cnt)
FROM cte
GROUP BY _user;
| _user | sum |
|---|
SELECT 0
SET enable_seqscan = OFF;
SET
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING)
WITH cte AS
(
SELECT
_user, COUNT(_user) AS cnt
FROM impressions
GROUP BY _user
UNION ALL
SELECT
_user, COUNT(_user)
FROM purchases
GROUP BY _user
UNION ALL
SELECT
_user, COUNT(_user)
FROM clicks
GROUP BY _user
)
SELECT _user, SUM(cnt)
FROM cte
GROUP BY _user;
| QUERY PLAN |
|---|
| Finalize GroupAggregate (cost=20000001156.05..20000001212.09 rows=200 width=48) (actual time=19.904..21.515 rows=0 loops=1) |
| Group Key: impressions._user |
| Buffers: shared hit=81 |
| -> Gather Merge (cost=20000001156.05..20000001206.59 rows=400 width=48) (actual time=19.902..21.513 rows=0 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=81 |
| -> Partial GroupAggregate (cost=20000000156.03..20000000160.40 rows=200 width=48) (actual time=0.125..0.128 rows=0 loops=3) |
| Group Key: impressions._user |
| Buffers: shared hit=81 |
| -> Sort (cost=20000000156.03..20000000156.65 rows=249 width=24) (actual time=0.124..0.127 rows=0 loops=3) |
| Sort Key: impressions._user |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=81 |
| Worker 0: Sort Method: quicksort Memory: 25kB |
| Worker 1: Sort Method: quicksort Memory: 25kB |
| -> Parallel Append (cost=10000000061.84..20000000146.12 rows=249 width=24) (actual time=0.015..0.017 rows=0 loops=3) |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.015..0.017 rows=0 loops=1) |
| Group Key: impressions._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.015..0.016 rows=0 loops=1) |
| Sort Key: impressions._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on impressions (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.003..0.003 rows=0 loops=1) |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.011..0.012 rows=0 loops=1) |
| Group Key: purchases._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.011..0.011 rows=0 loops=1) |
| Sort Key: purchases._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on purchases (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.003..0.003 rows=0 loops=1) |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.014..0.015 rows=0 loops=1) |
| Group Key: clicks._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.013..0.014 rows=0 loops=1) |
| Sort Key: clicks._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on clicks (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.005..0.005 rows=0 loops=1) |
| Settings: enable_seqscan = 'off', max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis' |
| Planning: |
| Buffers: shared hit=3 |
| Planning Time: 0.177 ms |
| Execution Time: 21.652 ms |
EXPLAIN
SELECT id, i.impressions_count, p.purchases_count, c.clicks_count
FROM (
SELECT "_user" AS id, count(*) AS impressions_count
FROM impressions
GROUP BY 1
) i
FULL JOIN (
SELECT "_user" AS id, count(*) AS purchases_count
FROM purchases
GROUP BY 1
) p USING (id)
FULL JOIN (
SELECT "_user" AS id, count(*) AS clicks_count
FROM clicks
GROUP BY 1
) c USING (id)
| id | impressions_count | purchases_count | clicks_count |
|---|
SELECT 0
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING)
SELECT id, i.impressions_count, p.purchases_count, c.clicks_count
FROM (
SELECT "_user" AS id, count(*) AS impressions_count
FROM impressions
GROUP BY 1
) i
FULL JOIN (
SELECT "_user" AS id, count(*) AS purchases_count
FROM purchases
GROUP BY 1
) p USING (id)
FULL JOIN (
SELECT "_user" AS id, count(*) AS clicks_count
FROM clicks
GROUP BY 1
) c USING (id)
| QUERY PLAN |
|---|
| Hash Full Join (cost=30000000198.61..30000000223.35 rows=200 width=40) (actual time=0.031..0.033 rows=0 loops=1) |
| Hash Cond: (COALESCE(impressions._user, purchases._user) = clicks._user) |
| -> Merge Full Join (cost=20000000123.68..20000000147.88 rows=200 width=48) (actual time=0.019..0.019 rows=0 loops=1) |
| Merge Cond: (impressions._user = purchases._user) |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.010..0.010 rows=0 loops=1) |
| Group Key: impressions._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.009..0.010 rows=0 loops=1) |
| Sort Key: impressions._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on impressions (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.003..0.003 rows=0 loops=1) |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.008..0.008 rows=0 loops=1) |
| Group Key: purchases._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.008..0.008 rows=0 loops=1) |
| Sort Key: purchases._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on purchases (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.002..0.003 rows=0 loops=1) |
| -> Hash (cost=10000000072.44..10000000072.44 rows=200 width=24) (actual time=0.008..0.008 rows=0 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 8kB |
| -> GroupAggregate (cost=10000000061.84..10000000070.44 rows=200 width=24) (actual time=0.007..0.008 rows=0 loops=1) |
| Group Key: clicks._user |
| -> Sort (cost=10000000061.84..10000000064.04 rows=880 width=16) (actual time=0.007..0.007 rows=0 loops=1) |
| Sort Key: clicks._user |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on clicks (cost=10000000000.00..10000000018.80 rows=880 width=16) (actual time=0.004..0.005 rows=0 loops=1) |
| Settings: enable_seqscan = 'off', max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis' |
| Planning: |
| Buffers: shared hit=3 |
| Planning Time: 0.153 ms |
| Execution Time: 0.070 ms |
EXPLAIN