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 (
update_date timestamp,
num_status int,
n_flag int
);
WITH RECURSIVE
cte AS (
SELECT 1 num
UNION ALL
SELECT num + 1 FROM cte WHERE num < 1000000
)
INSERT INTO test
SELECT ('1980-01-01'::date + (RANDOM() * 10000)::integer)::timestamp,
FLOOR(RANDOM() * 21),
FLOOR(RANDOM() * 6)
FROM cte;
SELECT * FROM test LIMIT 20;
1000000 rows affected
| update_date | num_status | n_flag |
|---|---|---|
| 1983-08-03 00:00:00 | 8 | 1 |
| 2004-11-05 00:00:00 | 1 | 0 |
| 2004-02-29 00:00:00 | 3 | 3 |
| 1992-06-20 00:00:00 | 16 | 0 |
| 2002-11-20 00:00:00 | 3 | 2 |
| 2000-06-19 00:00:00 | 14 | 0 |
| 1997-05-14 00:00:00 | 12 | 2 |
| 2001-07-31 00:00:00 | 4 | 0 |
| 1999-04-08 00:00:00 | 20 | 1 |
| 1991-04-27 00:00:00 | 2 | 2 |
| 2005-09-05 00:00:00 | 7 | 3 |
| 1999-03-02 00:00:00 | 9 | 0 |
| 2001-01-15 00:00:00 | 10 | 2 |
| 1994-01-17 00:00:00 | 13 | 1 |
| 2002-07-22 00:00:00 | 19 | 5 |
| 2004-02-13 00:00:00 | 19 | 1 |
| 1995-10-27 00:00:00 | 6 | 3 |
| 1981-07-19 00:00:00 | 3 | 4 |
| 1998-08-31 00:00:00 | 16 | 0 |
| 1985-01-28 00:00:00 | 0 | 3 |
CREATE INDEX idx1 ON test (n_flag, update_date, num_status);
select *
from test
where n_flag=0
and num_status>=10
order by update_date asc limit 1;
| update_date | num_status | n_flag |
|---|---|---|
| 1980-01-01 00:00:00 | 11 | 0 |
explain (analyze,buffers)
select *
from test
where n_flag=0
and num_status>=10
order by update_date asc limit 1;
| QUERY PLAN |
|---|
| Limit (cost=0.42..3.99 rows=1 width=16) (actual time=0.024..0.025 rows=1 loops=1) |
| Buffers: shared hit=4 |
| -> Index Only Scan using idx1 on test (cost=0.42..5947.10 rows=1667 width=16) (actual time=0.023..0.023 rows=1 loops=1) |
| Index Cond: ((n_flag = 0) AND (num_status >= 10)) |
| Heap Fetches: 1 |
| Buffers: shared hit=4 |
| Planning Time: 0.082 ms |
| Execution Time: 0.046 ms |