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?.
select version();
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE status_table(
name text NOT NULL PRIMARY KEY
,processed BOOLEAN NOT NULL
,processing BOOLEAN NOT NULL
,updated INTEGER NOT NULL
,ref_time INTEGER NOT NULL
);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('abc','t','f',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('def','f','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('ghi','t','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('jkl','t','f',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('mno','t','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('pqr','t','t',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('stu','t','t',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('vwx','t','t',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('cba','t','f',27794395,27794170);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('fed','f','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('ihg','t','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('lkj','t','f',27794395,27794170);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('onm','t','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('rqp','t','t',27794395,27794170);
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
select * from status_table order by ref_time desc;
| name | processed | processing | updated | ref_time |
|---|---|---|---|---|
| lkj | t | f | 27794395 | 27794170 |
| rqp | t | t | 27794395 | 27794170 |
| cba | t | f | 27794395 | 27794170 |
| stu | t | t | 27794395 | 27794160 |
| abc | t | f | 27794395 | 27794160 |
| vwx | t | t | 27794395 | 27794160 |
| jkl | t | f | 27794395 | 27794160 |
| pqr | t | t | 27794395 | 27794160 |
| fed | f | f | 27794395 | 27793450 |
| ihg | t | f | 27794395 | 27793450 |
| onm | t | f | 27794395 | 27793450 |
| mno | t | f | 27794395 | 27793440 |
| ghi | t | f | 27794395 | 27793440 |
| def | f | f | 27794395 | 27793440 |
SELECT 14
select ref_time,
count(*) cnt_total,
count(*) filter(where processed) cnt_processed,
round(avg(processed::int),2) ratio_processed
from status_table
group by ref_time order by ratio_processed desc, ref_time desc;
| ref_time | cnt_total | cnt_processed | ratio_processed |
|---|---|---|---|
| 27794170 | 3 | 3 | 1.00 |
| 27794160 | 5 | 5 | 1.00 |
| 27793450 | 3 | 2 | 0.67 |
| 27793440 | 3 | 2 | 0.67 |
SELECT 4
with
ref as (
select ref_time
from status_table
group by ref_time
having bool_and(processed)
order by ref_time desc
offset 1
)
delete from status_table s
using ref r
where s.ref_time = r.ref_time
DELETE 5
select ref_time,
count(*) cnt_total,
count(*) filter(where processed) cnt_processed,
round(avg(processed::int),2) ratio_processed
from status_table
group by ref_time order by ratio_processed desc, ref_time desc;
| ref_time | cnt_total | cnt_processed | ratio_processed |
|---|---|---|---|
| 27794170 | 3 | 3 | 1.00 |
| 27793450 | 3 | 2 | 0.67 |
| 27793440 | 3 | 2 | 0.67 |
SELECT 3