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 status_table (
name text NOT NULL PRIMARY KEY
, version int NOT NULL
, processed bool NOT NULL
, processing bool NOT NULL
, updated int NOT NULL
, ref_time int NOT NULL
);
INSERT INTO status_table VALUES
('abc', 1, 't', 'f', 27794395, 27794160)
, ('def', 1, 'f', 'f', 27794395, 27793440)
, ('ghi', 1, 't', 'f', 27794395, 27793440)
, ('jkl', 1, 't', 'f', 27794395, 27794160)
, ('mno', 1, 't', 'f', 27794395, 27793440)
, ('pqr', 1, 't', 't', 27794395, 27794160)
, ('cba', 1, 't', 'f', 27794395, 27794170)
, ('fed', 1, 'f', 'f', 27794395, 27793450)
, ('ihg', 1, 't', 'f', 27794395, 27793450)
, ('lkj', 1, 't', 'f', 27794395, 27794170)
, ('onm', 1, 't', 'f', 27794395, 27793450)
, ('rqp', 1, 't', 't', 27794395, 27794170)
-- one more fully processed to have 4
, ('666', 1, 't', 'f', 27794399, 27794145)
-- vary count
, ('pqg', 1, 't', 't', 27794395, 27794160)
, ('pqh', 1, 't', 't', 27794395, 27794150)
, ('pqi', 1, 't', 't', 27794395, 27794150)
;
CREATE TABLE
INSERT 0 16
SELECT ref_time
, count(*) AS total
, count(*) FILTER (WHERE processed) AS processed
, round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 2) AS percent
FROM status_table
GROUP BY 1
ORDER BY percent DESC, ref_time DESC;
| ref_time | total | processed | percent |
|---|---|---|---|
| 27794170 | 3 | 3 | 100.00 |
| 27794160 | 4 | 4 | 100.00 |
| 27794150 | 2 | 2 | 100.00 |
| 27794145 | 1 | 1 | 100.00 |
| 27793450 | 3 | 2 | 66.67 |
| 27793440 | 3 | 2 | 66.67 |
SELECT 6
-- Demonstrate selection process
SELECT *, count(*) FILTER (WHERE drop) OVER (ORDER BY ref_time DESC) AS drops
FROM (
SELECT ref_time, count(*)
, lag(count(*)) OVER (ORDER BY ref_time DESC) >= count(*) IS TRUE AS drop
FROM status_table
GROUP BY ref_time
HAVING bool_and(processed)
ORDER BY ref_time DESC, drop
) sub;
| ref_time | count | drop | drops |
|---|---|---|---|
| 27794170 | 3 | f | 0 |
| 27794160 | 4 | f | 0 |
| 27794150 | 2 | t | 1 |
| 27794145 | 1 | t | 2 |
SELECT 4
WITH sel AS (
SELECT ref_time
FROM (
SELECT ref_time
, count(*) FILTER (WHERE drop) OVER (ORDER BY ref_time DESC) AS drops
FROM (
SELECT ref_time
, lag(count(*)) OVER (ORDER BY ref_time DESC) >= count(*) IS TRUE AS drop
FROM status_table
GROUP BY ref_time
HAVING bool_and(processed)
) sub1
) sub2
WHERE drops > 0
)
DELETE FROM status_table d
USING sel s
WHERE d.ref_time = s.ref_time;
DELETE 3
SELECT ref_time
, count(*) AS total
, count(*) FILTER (WHERE processed) AS processed
, round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 2) AS percent
FROM status_table
GROUP BY 1
ORDER BY percent DESC, ref_time DESC;
| ref_time | total | processed | percent |
|---|---|---|---|
| 27794170 | 3 | 3 | 100.00 |
| 27794160 | 4 | 4 | 100.00 |
| 27793450 | 3 | 2 | 66.67 |
| 27793440 | 3 | 2 | 66.67 |
SELECT 4