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 |
|---|---|---|
| 1981-09-09 00:00:00 | 16 | 4 |
| 1993-03-09 00:00:00 | 10 | 1 |
| 1984-08-09 00:00:00 | 15 | 2 |
| 1990-07-13 00:00:00 | 7 | 4 |
| 2001-08-23 00:00:00 | 14 | 2 |
| 1985-07-08 00:00:00 | 20 | 1 |
| 1995-11-05 00:00:00 | 6 | 3 |
| 2004-07-24 00:00:00 | 10 | 5 |
| 1997-03-01 00:00:00 | 8 | 1 |
| 1987-12-31 00:00:00 | 4 | 0 |
| 1993-12-28 00:00:00 | 16 | 4 |
| 2000-11-15 00:00:00 | 0 | 5 |
| 1988-07-02 00:00:00 | 4 | 3 |
| 1994-02-21 00:00:00 | 0 | 0 |
| 1999-01-08 00:00:00 | 13 | 2 |
| 2006-04-20 00:00:00 | 0 | 4 |
| 1997-10-26 00:00:00 | 1 | 0 |
| 1984-02-22 00:00:00 | 0 | 4 |
| 2002-01-09 00:00:00 | 15 | 2 |
| 1987-09-21 00:00:00 | 12 | 2 |
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.100 ms |
| Execution Time: 0.046 ms |
DROP INDEX idx1;
CREATE INDEX idx1 ON test (n_flag, update_date);
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..8.74 rows=1 width=16) (actual time=0.094..0.095 rows=1 loops=1) |
| Buffers: shared read=4 |
| -> Index Scan using idx1 on test (cost=0.42..13856.42 rows=1667 width=16) (actual time=0.091..0.092 rows=1 loops=1) |
| Index Cond: (n_flag = 0) |
| Filter: (num_status >= 10) |
| Buffers: shared read=4 |
| Planning Time: 0.402 ms |
| Execution Time: 0.127 ms |