add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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
setseed 
SELECT 1

INSERT 0 20000
length pk column1
630 192 0.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.731480.73148
630 222 0.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.382630.38263
630 574 0.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.081670.08167
630 1058 0.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.083050.08305
630 1187 0.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.854630.85463 
SELECT 5

ERROR:  column reference "column1" is ambiguous
LINE 6:     AND a.md5(column1) = b.md5(column1);
                      ^

VACUUM

BEGIN
QUERY PLAN
Delete on public.my_table a (cost=3758.00..10978.49 rows=7480 width=650) (actual time=26.669..68.048 rows=1858 loops=1)
  Output: a.pk, a.column1, b.pk, b.column1
  -> Hash Join (cost=3758.00..10978.49 rows=7480 width=650) (actual time=26.642..64.441 rows=1985 loops=1)
        Output: a.ctid, b.ctid, b.pk, b.column1
        Hash Cond: (a.column1 = b.column1)
        Join Filter: (a.pk < b.pk)
        Rows Removed by Join Filter: 21985
        -> Seq Scan on public.my_table a (cost=0.00..1867.00 rows=20000 width=644) (actual time=0.006..4.122 rows=20000 loops=1)
              Output: a.ctid, a.pk, a.column1
        -> Hash (cost=1867.00..1867.00 rows=20000 width=644) (actual time=26.444..26.445 rows=20000 loops=1)
              Output: b.ctid, b.pk, b.column1
              Buckets: 16384 Batches: 2 Memory Usage: 6917kB
              -> Seq Scan on public.my_table b (cost=0.00..1867.00 rows=20000 width=644) (actual time=0.004..4.218 rows=20000 loops=1)
                    Output: b.ctid, b.pk, b.column1
Planning Time: 1.089 ms
Execution Time: 68.541 ms 
EXPLAIN

ROLLBACK

BEGIN
QUERY PLAN
Delete on public.my_table a (cost=18078.54..63328.54 rows=666667 width=650) (actual time=215.633..249.269 rows=1858 loops=1)
  Output: a.pk, a.column1, b.pk, b.column1
  -> Merge Join (cost=18078.54..63328.54 rows=666667 width=650) (actual time=215.603..246.022 rows=1985 loops=1)
        Output: a.ctid, b.ctid, b.pk, b.column1
        Merge Cond: ((md5(a.column1)) = (md5(b.column1)))
        Join Filter: (a.pk < b.pk)
        Rows Removed by Join Filter: 21985
        -> Sort (cost=9039.27..9089.27 rows=20000 width=644) (actual time=84.599..93.143 rows=20000 loops=1)
              Output: a.ctid, a.pk, a.column1, (md5(a.column1))
              Sort Key: (md5(a.column1))
              Sort Method: external merge Disk: 13496kB
              -> Seq Scan on public.my_table a (cost=0.00..1867.00 rows=20000 width=644) (actual time=0.380..50.858 rows=20000 loops=1)
                    Output: a.ctid, a.pk, a.column1, md5(a.column1)
        -> Materialize (cost=9039.27..9139.27 rows=20000 width=644) (actual time=130.986..143.272 rows=23970 loops=1)
              Output: b.ctid, b.pk, b.column1, (md5(b.column1))
              -> Sort (cost=9039.27..9089.27 rows=20000 width=644) (actual time=130.981..139.465 rows=20000 loops=1)
                    Output: b.ctid, b.pk, b.column1, (md5(b.column1))
                    Sort Key: (md5(b.column1))
                    Sort Method: external merge Disk: 13496kB
                    -> Seq Scan on public.my_table b (cost=0.00..1867.00 rows=20000 width=644) (actual time=0.030..50.704 rows=20000 loops=1)
                          Output: b.ctid, b.pk, b.column1, md5(b.column1)
Planning Time: 0.131 ms
Execution Time: 252.893 ms 
EXPLAIN

ROLLBACK