Postgres 13

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 city(
   city_id serial primary key
 , city text COLLATE "C"
 , other_names_lower text COLLATE "C"
);

INSERT INTO city (city, other_names_lower)
SELECT t, t  -- second column just to make row bigger
FROM (
   SELECT left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int)
          || ' ' ||
          left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int) AS t
   FROM   generate_series(1, 110000) g
   GROUP  BY g%11000  -- random words
   ) sub;

INSERT INTO city (city, other_names_lower)
VALUES ('%foobar','%fooелезbar');

DELETE FROM city WHERE random() > 0.9;  -- some dead rows

ANALYZE city;

CREATE TABLE



INSERT 0 11000



INSERT 0 1



DELETE 1063



ANALYZE


SELECT ctid, * FROM city ORDER BY ctid DESC LIMIT 3;


ctid
city_id
city
other_names_lower


(87,87)
11001
%foobar
%fooелезbar

(87,86)
11000
QOWAJNJL YET
QOWAJNJL YET

(87,85)
10999
QJDPP ZVMTIXT
QJDPP ZVMTIXT



SELECT 3


DELETE FROM city c
USING (
   SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
        , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid  -- max uint2 65535 = 2^16 - 1   
   FROM   city
   ORDER  BY ctid DESC
   LIMIT  1
   ) d
WHERE  c.ctid BETWEEN min_tid AND max_tid;

DELETE 80


SELECT ctid, * FROM city ORDER BY ctid DESC LIMIT 3;


ctid
city_id
city
other_names_lower


(86,126)
10914
FGWCZJIH DWO
FGWCZJIH DWO

(86,125)
10913
NTUVXCUM IBDVTZA
NTUVXCUM IBDVTZA

(86,124)
10912
CPCHIF VSDJPP
CPCHIF VSDJPP



SELECT 3

ctid	city_id	city	other_names_lower
(87,87)	11001	%foobar	%fooелезbar
(87,86)	11000	QOWAJNJL YET	QOWAJNJL YET
(87,85)	10999	QJDPP ZVMTIXT	QJDPP ZVMTIXT

ctid	city_id	city	other_names_lower
(86,126)	10914	FGWCZJIH DWO	FGWCZJIH DWO
(86,125)	10913	NTUVXCUM IBDVTZA	NTUVXCUM IBDVTZA
(86,124)	10912	CPCHIF VSDJPP	CPCHIF VSDJPP