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 t1 AS
(
SELECT
id
FROM
generate_series(1, 1000000) AS s(id)
ORDER BY
random()
LIMIT
50000
) ;
ALTER TABLE IF EXISTS public.t1
ADD PRIMARY KEY (id);
ANALYZE t1 ;
DROP TABLE IF EXISTS t2 ;
CREATE TABLE t2 AS
(
SELECT
id
FROM
generate_series(1, 1000000) AS s(id)
ORDER BY
random()
LIMIT
20000
) ;
EXPLAIN ANALYZE VERBOSE
SELECT
id AS id_in_t2_not_in_t1
FROM
t2
WHERE
t2.id::integer NOT IN (SELECT id FROM t1) ;
SELECT 50000
ALTER TABLE
ANALYZE
DROP TABLE
SELECT 20000
| QUERY PLAN |
|---|
| Seq Scan on public.t2 (cost=847.00..1219.69 rows=11348 width=4) (actual time=33.189..38.629 rows=19052 loops=1) |
| Output: t2.id |
| Filter: (NOT (hashed SubPlan 1)) |
| Rows Removed by Filter: 948 |
| SubPlan 1 |
| -> Seq Scan on public.t1 (cost=0.00..722.00 rows=50000 width=4) (actual time=0.013..6.657 rows=50000 loops=1) |
| Output: t1.id |
| Planning Time: 0.287 ms |
| Execution Time: 39.984 ms |
EXPLAIN