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 test (
bar int not null,
baz int not null
);
CREATE TABLE
with bar_vals as (
select a.n
from generate_series(1,10) as a(n), generate_series(1,100)
),
baz_vals as (
select a.n
from generate_series(1,10) as a(n), generate_series(1,100)
)
insert into test
select bar_vals.*, baz_vals.*
from bar_vals, baz_vals;
INSERT 0 1000000
create index bar_baz_idx on test(bar, baz);
CREATE INDEX
create index baz_idx on test(baz);
CREATE INDEX
explain SELECT *
FROM test
WHERE bar <> 5 OR baz <> 8
| QUERY PLAN |
|---|
| Seq Scan on test (cost=0.00..19425.00 rows=999975 width=8) |
| Filter: ((bar <> 5) OR (baz <> 8)) |
EXPLAIN
explain SELECT *
FROM test
WHERE bar <> 5
INTERSECT
SELECT *
FROM test
WHERE baz <> 8
| QUERY PLAN |
|---|
| HashSetOp Intersect (cost=0.00..73650.00 rows=40000 width=12) |
| -> Append (cost=0.00..63700.00 rows=1990000 width=12) |
| -> Subquery Scan on "*SELECT* 1" (cost=0.00..26875.00 rows=995000 width=12) |
| -> Seq Scan on test (cost=0.00..16925.00 rows=995000 width=8) |
| Filter: (bar <> 5) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.00..26875.00 rows=995000 width=12) |
| -> Seq Scan on test test_1 (cost=0.00..16925.00 rows=995000 width=8) |
| Filter: (baz <> 8) |
EXPLAIN
explain SELECT *
FROM test
WHERE NOT (bar = 5 and baz = 8)
| QUERY PLAN |
|---|
| Seq Scan on test (cost=0.00..19425.00 rows=999975 width=8) |
| Filter: ((bar <> 5) OR (baz <> 8)) |
EXPLAIN