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 IF NOT EXISTS public.creative_schedule_status_histories (
id serial PRIMARY KEY,
"creativeScheduleId" text NOT NULL
-- other columns
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_creativescheduleid_id
ON public.creative_schedule_status_histories ("creativeScheduleId" ASC, id ASC);
CREATE TABLE
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT
q1.id,
q1."creativeScheduleId"
FROM
(
SELECT
cssh.*,
CASE
WHEN cssh."creativeScheduleId" =
LAST_VALUE(cssh."creativeScheduleId") OVER (
ORDER BY cssh."creativeScheduleId" DESC, cssh.id DESC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
THEN 0
ELSE 1
END AS qualified
FROM public.creative_schedule_status_histories AS cssh
) AS q1
WHERE
q1.qualified = 1;
| QUERY PLAN |
|---|
| Subquery Scan on q1 (cost=0.15..104.48 rows=6 width=36) (actual time=0.016..0.017 rows=0 loops=1) |
| Filter: (q1.qualified = 1) |
| -> WindowAgg (cost=0.15..88.60 rows=1270 width=40) (actual time=0.015..0.016 rows=0 loops=1) |
| -> Index Only Scan Backward using idx_creativescheduleid_id on creative_schedule_status_histories cssh (cost=0.15..63.20 rows=1270 width=36) (actual time=0.013..0.013 rows=0 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.467 ms |
| Execution Time: 0.074 ms |
EXPLAIN