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 scanners (
"time" time without time zone,
"scanner" text,
"type" text,
"parcel" int
);
INSERT INTO scanners VALUES
('12:00:19.635', 's15', 'Sort', 83128),
('12:00:20.3', 's15', 'Confirm', 83128),
('12:00:19.635', 's80', 'Sort', 83128),
('12:00:20.3', 's80', 'Confirm', 83128),
('12:02:05.857', 's15', 'Sort', 94387),
('12:02:07.692', 's15', 'Confirm', 94387),
('12:02:05.857', 's18', 'Sort', 94387),
('12:02:07.692', 's18', 'Confirm', 94387),
('12:03:17.857', 's71', 'Sort', 94387),
('12:03:21.692', 's71', 'Confirm', 94387);
SELECT * FROM scanners;
10 rows affected
| time | scanner | type | parcel |
|---|---|---|---|
| 12:00:19.635 | s15 | Sort | 83128 |
| 12:00:20.3 | s15 | Confirm | 83128 |
| 12:00:19.635 | s80 | Sort | 83128 |
| 12:00:20.3 | s80 | Confirm | 83128 |
| 12:02:05.857 | s15 | Sort | 94387 |
| 12:02:07.692 | s15 | Confirm | 94387 |
| 12:02:05.857 | s18 | Sort | 94387 |
| 12:02:07.692 | s18 | Confirm | 94387 |
| 12:03:17.857 | s71 | Sort | 94387 |
| 12:03:21.692 | s71 | Confirm | 94387 |
SELECT parcel, passed_scanners
FROM (
SELECT
parcel,
array_agg(scanner) as passed_scanners
FROM
scanners
GROUP BY parcel
)s
WHERE 's15' = ANY(passed_scanners) AND NOT (ARRAY['s71', 's72'] && passed_scanners)
| parcel | passed_scanners |
|---|---|
| 83128 | {s15,s15,s80,s80} |
SELECT parcel, duration
FROM (
SELECT
parcel,
MAX(time) FILTER (WHERE scanner IN ('s71', 's72')) -
MIN(time) FILTER (WHERE scanner = 's15') AS duration,
array_agg(scanner) as passed_scanners
FROM
scanners
GROUP BY parcel
)s
WHERE 's15' = ANY(passed_scanners) AND (ARRAY['s71', 's72'] && passed_scanners)
| parcel | duration |
|---|---|
| 94387 | 00:01:15.835 |