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 setseed(.42);
create table my_table(id,user_id,group_id,c1,c2,c3,deleted_at)as
select n,(random()*1e5)::int as user_id
,(random()*1e4)::int as group_id
,(random()*1e5)::int as c1
,(random()*1e5)::int as c2
,(random()*1e5)::int as c3
,case when .8>random() then now()-'1 year'::interval end as deleted_at
from generate_series(1,3e5)n;
create index idx1 on my_table(group_id);
| setseed |
|---|
SELECT 1
SELECT 300000
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null
| QUERY PLAN |
|---|
| Aggregate (cost=114.95..114.96 rows=1 width=8) (actual time=0.172..0.173 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.52..114.94 rows=6 width=4) (actual time=0.103..0.148 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NULL) |
| Rows Removed by Filter: 30 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.079..0.079 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.171 ms |
| Execution Time: 0.259 ms |
EXPLAIN
--1. An index on the expression
CREATE INDEX deleted_at_is_null ON my_table ((deleted_at is null));
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=114.95..114.96 rows=1 width=8) (actual time=0.094..0.095 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.52..114.94 rows=6 width=4) (actual time=0.038..0.082 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NULL) |
| Rows Removed by Filter: 30 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.014..0.014 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.148 ms |
| Execution Time: 0.184 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.00..115.01 rows=1 width=8) (actual time=0.062..0.062 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=24 width=4) (actual time=0.018..0.050 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NOT NULL) |
| Rows Removed by Filter: 6 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.010..0.010 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.064 ms |
| Execution Time: 0.111 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.02..115.03 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=30 width=4) (actual time=0.017..0.046 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.010..0.010 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.112 ms |
| Execution Time: 0.090 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.01..115.02 rows=1 width=8) (actual time=0.061..0.062 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=25 width=4) (actual time=0.018..0.049 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: ((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.009..0.010 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.066 ms |
| Execution Time: 0.092 ms |
EXPLAIN
--2. Replace each of the current indexes (like the ones on user_id and group_id above) with composite indexes on that column, plus deleted_at is null.
--expression on 2nd position
drop index idx1;
CREATE INDEX idx2 ON my_table (group_id,(deleted_at is null));
DROP INDEX
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=27.68..27.69 rows=1 width=8) (actual time=0.084..0.085 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.48..27.67 rows=6 width=4) (actual time=0.061..0.071 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NULL) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx2 (cost=0.00..4.48 rows=6 width=0) (actual time=0.051..0.052 rows=6 loops=1) |
| Index Cond: ((my_table.group_id = 123) AND ((my_table.deleted_at IS NULL) = true)) |
| Planning Time: 0.158 ms |
| Execution Time: 0.119 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.13..115.14 rows=1 width=8) (actual time=0.104..0.105 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.65..115.07 rows=24 width=4) (actual time=0.041..0.091 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NOT NULL) |
| Rows Removed by Filter: 6 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx2 (cost=0.00..4.65 rows=30 width=0) (actual time=0.031..0.031 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.063 ms |
| Execution Time: 0.133 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.14..115.15 rows=1 width=8) (actual time=0.057..0.058 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.66..115.07 rows=30 width=4) (actual time=0.017..0.044 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx2 (cost=0.00..4.65 rows=30 width=0) (actual time=0.010..0.010 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.057 ms |
| Execution Time: 0.084 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.13..115.14 rows=1 width=8) (actual time=4.111..4.112 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.65..115.07 rows=25 width=4) (actual time=4.067..4.098 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: ((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx2 (cost=0.00..4.65 rows=30 width=0) (actual time=4.057..4.057 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.063 ms |
| Execution Time: 4.140 ms |
EXPLAIN
drop index if exists idx1;
drop index if exists idx2;
--2. expression on 1st position
CREATE INDEX idx2 ON my_table ((deleted_at is null),group_id);
DROP INDEX
DROP INDEX
CREATE INDEX
vacuum analyze my_table;
VACUUM
--nice to see index scans all over, but exec times aren't in general as good as before
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=23.55..23.56 rows=1 width=8) (actual time=0.065..0.066 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Index Scan using idx2 on public.my_table (cost=0.42..23.53 rows=6 width=4) (actual time=0.042..0.050 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Index Cond: (((my_table.deleted_at IS NULL) = true) AND (my_table.group_id = 123)) |
| Planning Time: 0.147 ms |
| Execution Time: 0.104 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=3585.71..3585.72 rows=1 width=8) (actual time=3.558..3.558 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Index Scan using idx2 on public.my_table (cost=0.42..3585.65 rows=24 width=4) (actual time=0.070..3.541 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Index Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NOT NULL) |
| Rows Removed by Filter: 6 |
| Planning Time: 0.060 ms |
| Execution Time: 3.579 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=3585.73..3585.74 rows=1 width=8) (actual time=0.556..0.557 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Index Scan using idx2 on public.my_table (cost=0.42..3585.65 rows=30 width=4) (actual time=0.014..0.540 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.068 ms |
| Execution Time: 0.578 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=3585.72..3585.73 rows=1 width=8) (actual time=0.525..0.526 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Index Scan using idx2 on public.my_table (cost=0.42..3585.65 rows=25 width=4) (actual time=0.012..0.512 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Index Cond: (my_table.group_id = 123) |
| Filter: ((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) |
| Planning Time: 0.083 ms |
| Execution Time: 0.545 ms |
EXPLAIN
--3. Same as 2, but instead of replacing the indexes, add the composite indexes in addition to the currently-existing indexes. This feels wrong and redundant, but I am not sure.
create index idx1 on my_table(group_id);
drop index if exists idx2;
CREATE INDEX idx2 ON my_table (group_id,(deleted_at is null));
CREATE INDEX
DROP INDEX
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=27.68..27.69 rows=1 width=8) (actual time=0.070..0.071 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.48..27.67 rows=6 width=4) (actual time=0.047..0.057 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NULL) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx2 (cost=0.00..4.48 rows=6 width=0) (actual time=0.038..0.039 rows=6 loops=1) |
| Index Cond: ((my_table.group_id = 123) AND ((my_table.deleted_at IS NULL) = true)) |
| Planning Time: 0.184 ms |
| Execution Time: 0.105 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.00..115.01 rows=1 width=8) (actual time=0.118..0.118 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=24 width=4) (actual time=0.035..0.105 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NOT NULL) |
| Rows Removed by Filter: 6 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.025..0.025 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.065 ms |
| Execution Time: 0.145 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.02..115.03 rows=1 width=8) (actual time=0.053..0.054 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=30 width=4) (actual time=0.015..0.041 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.009..0.009 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.060 ms |
| Execution Time: 0.079 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.01..115.02 rows=1 width=8) (actual time=0.055..0.056 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=25 width=4) (actual time=0.016..0.045 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: ((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.008..0.009 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.066 ms |
| Execution Time: 0.083 ms |
EXPLAIN
--4. Add a new partial index for each of the currently-existing indexes with a where deleted_at is not null condition. Like number 3, this feels like too many indexes.
create index idx4 on my_table(group_id)where(deleted_at is null);
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=27.54..27.55 rows=1 width=8) (actual time=0.061..0.062 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.34..27.52 rows=6 width=4) (actual time=0.037..0.046 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: ((my_table.group_id = 123) AND (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx4 (cost=0.00..4.33 rows=6 width=0) (actual time=0.030..0.030 rows=6 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.344 ms |
| Execution Time: 0.095 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.00..115.01 rows=1 width=8) (actual time=0.083..0.084 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=24 width=4) (actual time=0.021..0.072 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: (my_table.deleted_at IS NOT NULL) |
| Rows Removed by Filter: 6 |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.011..0.012 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.075 ms |
| Execution Time: 0.112 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.02..115.03 rows=1 width=8) (actual time=0.057..0.058 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=30 width=4) (actual time=0.016..0.044 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.009..0.009 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.068 ms |
| Execution Time: 0.085 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=115.01..115.02 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.53..114.94 rows=25 width=4) (actual time=0.017..0.047 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (my_table.group_id = 123) |
| Filter: ((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=36 |
| -> Bitmap Index Scan on idx1 (cost=0.00..4.52 rows=30 width=0) (actual time=0.009..0.009 rows=36 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.077 ms |
| Execution Time: 0.088 ms |
EXPLAIN
--5. replace the base index with a partial using opposite predicate
drop index if exists idx1;
drop index if exists idx2;
drop index if exists idx3;
drop index if exists idx4;
drop index if exists deleted_at_is_null;
create index idx4_a on my_table(group_id)where(deleted_at is null);
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=27.54..27.55 rows=1 width=8) (actual time=0.383..0.384 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.34..27.52 rows=6 width=4) (actual time=0.355..0.365 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: ((my_table.group_id = 123) AND (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx4_a (cost=0.00..4.33 rows=6 width=0) (actual time=0.347..0.347 rows=6 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.623 ms |
| Execution Time: 0.503 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=5657.34..5657.35 rows=1 width=8) (actual time=78.825..78.917 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Gather (cost=1000.00..5657.28 rows=24 width=4) (actual time=1.244..78.868 rows=30 loops=1) |
| Output: user_id |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Parallel Seq Scan on public.my_table (cost=0.00..4654.88 rows=14 width=4) (actual time=0.990..54.217 rows=15 loops=2) |
| Output: user_id |
| Filter: ((my_table.deleted_at IS NOT NULL) AND (my_table.group_id = 123)) |
| Rows Removed by Filter: 149985 |
| Worker 0: actual time=1.005..29.958 rows=3 loops=1 |
| Planning Time: 0.754 ms |
| Execution Time: 87.580 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=5657.96..5657.97 rows=1 width=8) (actual time=70.479..70.544 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Gather (cost=1000.00..5657.88 rows=30 width=4) (actual time=0.951..70.502 rows=36 loops=1) |
| Output: user_id |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Parallel Seq Scan on public.my_table (cost=0.00..4654.88 rows=18 width=4) (actual time=0.977..38.691 rows=18 loops=2) |
| Output: user_id |
| Filter: (my_table.group_id = 123) |
| Rows Removed by Filter: 149982 |
| Worker 0: actual time=1.200..20.341 rows=7 loops=1 |
| Planning Time: 0.064 ms |
| Execution Time: 70.572 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=5657.44..5657.45 rows=1 width=8) (actual time=80.733..80.819 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Gather (cost=1000.00..5657.38 rows=25 width=4) (actual time=1.123..80.777 rows=36 loops=1) |
| Output: user_id |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Parallel Seq Scan on public.my_table (cost=0.00..4654.88 rows=15 width=4) (actual time=1.140..55.836 rows=18 loops=2) |
| Output: user_id |
| Filter: (((my_table.deleted_at IS NOT NULL) OR (my_table.deleted_at IS NULL)) AND (my_table.group_id = 123)) |
| Rows Removed by Filter: 149982 |
| Worker 0: actual time=1.335..31.192 rows=5 loops=1 |
| Planning Time: 0.078 ms |
| Execution Time: 80.860 ms |
EXPLAIN
--5. replace the base index with two opposite, mutually supplementing partials
drop index if exists idx1;
drop index if exists idx2;
drop index if exists idx3;
drop index if exists idx4;
drop index if exists idx4_a;
drop index if exists idx4_b;
drop index if exists deleted_at_is_null;
create index idx4_a on my_table(group_id)where(deleted_at is null);
create index idx4_b on my_table(group_id)where(deleted_at is not null);
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
CREATE INDEX
CREATE INDEX
vacuum analyze my_table;
VACUUM
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is not null;
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123';
explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and (deleted_at is not null or deleted_at is null);
| QUERY PLAN |
|---|
| Aggregate (cost=27.54..27.55 rows=1 width=8) (actual time=0.086..0.087 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.34..27.52 rows=6 width=4) (actual time=0.062..0.071 rows=6 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: ((my_table.group_id = 123) AND (my_table.deleted_at IS NULL)) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx4_a (cost=0.00..4.33 rows=6 width=0) (actual time=0.055..0.055 rows=6 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.313 ms |
| Execution Time: 0.121 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=93.71..93.72 rows=1 width=8) (actual time=0.091..0.092 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=4.48..93.65 rows=24 width=4) (actual time=0.034..0.077 rows=30 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: ((my_table.group_id = 123) AND (my_table.deleted_at IS NOT NULL)) |
| Heap Blocks: exact=30 |
| -> Bitmap Index Scan on idx4_b (cost=0.00..4.47 rows=24 width=0) (actual time=0.025..0.026 rows=30 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.067 ms |
| Execution Time: 0.119 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=5657.96..5657.97 rows=1 width=8) (actual time=68.708..68.772 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Gather (cost=1000.00..5657.88 rows=30 width=4) (actual time=0.923..68.733 rows=36 loops=1) |
| Output: user_id |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Parallel Seq Scan on public.my_table (cost=0.00..4654.88 rows=18 width=4) (actual time=1.160..37.863 rows=18 loops=2) |
| Output: user_id |
| Filter: (my_table.group_id = 123) |
| Rows Removed by Filter: 149982 |
| Worker 0: actual time=1.602..20.056 rows=7 loops=1 |
| Planning Time: 0.066 ms |
| Execution Time: 68.795 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Aggregate (cost=119.30..119.31 rows=1 width=8) (actual time=0.096..0.097 rows=1 loops=1) |
| Output: count(DISTINCT user_id) |
| -> Bitmap Heap Scan on public.my_table (cost=8.82..119.24 rows=25 width=4) (actual time=0.032..0.082 rows=36 loops=1) |
| Output: id, user_id, group_id, c1, c2, c3, deleted_at |
| Recheck Cond: (((my_table.group_id = 123) AND (my_table.deleted_at IS NOT NULL)) OR ((my_table.group_id = 123) AND (my_table.deleted_at IS NULL))) |
| Heap Blocks: exact=36 |
| -> BitmapOr (cost=8.82..8.82 rows=30 width=0) (actual time=0.021..0.021 rows=0 loops=1) |
| -> Bitmap Index Scan on idx4_b (cost=0.00..4.47 rows=24 width=0) (actual time=0.016..0.016 rows=30 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| -> Bitmap Index Scan on idx4_a (cost=0.00..4.33 rows=6 width=0) (actual time=0.004..0.004 rows=6 loops=1) |
| Index Cond: (my_table.group_id = 123) |
| Planning Time: 0.099 ms |
| Execution Time: 0.136 ms |
EXPLAIN