Postgres 12

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 account (id int, name text);
INSERT INTO account VALUES(generate_series(0,1050),'untouched record..');

CREATE TABLE



INSERT 0 1051


DO $$ 
DECLARE 
  page int := 100;
  min_id bigint; max_id bigint;
BEGIN
  SELECT max(id),min(id) INTO max_id,min_id FROM account;
  FOR j IN min_id..max_id BY page LOOP 
    UPDATE account SET name = now() ||' -> UPDATED ' || j  || ' to ' || j+page
    WHERE id >= j AND id < j+page;
    -- I would get rid of this RAISE INFO in production.
    RAISE INFO 'committing data from % to % at %', j,j+page,now();
    COMMIT;            
  END LOOP;
END; $$;

DO


SELECT DISTINCT ON (name) name, count(id)
FROM account 
GROUP BY name ORDER BY name;


name
count


2024-05-06 08:40:50.589646+01 -> UPDATED 0 to 100
100

2024-05-06 08:40:50.631665+01 -> UPDATED 100 to 200
100

2024-05-06 08:40:50.639107+01 -> UPDATED 200 to 300
100

2024-05-06 08:40:50.643113+01 -> UPDATED 300 to 400
100

2024-05-06 08:40:50.647261+01 -> UPDATED 400 to 500
100

2024-05-06 08:40:50.649653+01 -> UPDATED 500 to 600
100

2024-05-06 08:40:50.655106+01 -> UPDATED 600 to 700
100

2024-05-06 08:40:50.659493+01 -> UPDATED 700 to 800
100

2024-05-06 08:40:50.667124+01 -> UPDATED 800 to 900
100

2024-05-06 08:40:50.675118+01 -> UPDATED 900 to 1000
100

2024-05-06 08:40:50.679104+01 -> UPDATED 1000 to 1100
51



SELECT 11

name	count
2024-05-06 08:40:50.589646+01 -> UPDATED 0 to 100	100
2024-05-06 08:40:50.631665+01 -> UPDATED 100 to 200	100
2024-05-06 08:40:50.639107+01 -> UPDATED 200 to 300	100
2024-05-06 08:40:50.643113+01 -> UPDATED 300 to 400	100
2024-05-06 08:40:50.647261+01 -> UPDATED 400 to 500	100
2024-05-06 08:40:50.649653+01 -> UPDATED 500 to 600	100
2024-05-06 08:40:50.655106+01 -> UPDATED 600 to 700	100
2024-05-06 08:40:50.659493+01 -> UPDATED 700 to 800	100
2024-05-06 08:40:50.667124+01 -> UPDATED 800 to 900	100
2024-05-06 08:40:50.675118+01 -> UPDATED 900 to 1000	100
2024-05-06 08:40:50.679104+01 -> UPDATED 1000 to 1100	51