Postgres 15

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 t_distinct (
id INT NOT NULL PRIMARY KEY,
orderer INT NOT NULL,
glow INT NOT NULL,
ghigh INT NOT NULL,
stuffing VARCHAR(200) NOT NULL
);
 
CREATE INDEX ix_distinct_glow_id ON t_distinct (glow, id);
CREATE INDEX ix_distinct_ghigh_id ON t_distinct (ghigh, id);
CREATE INDEX ix_distinct_glow_orderer_id ON t_distinct (glow, orderer, id);
CREATE INDEX ix_distinct_ghigh_orderer_id ON t_distinct (ghigh, orderer, id);

CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX


SELECT  SETSEED(0.20091126);


setseed






SELECT 1


INSERT
INTO    t_distinct (id, orderer, glow, ghigh, stuffing)
SELECT  id, FLOOR(RANDOM() * 9) + 1,
(id - 1) % 10 + 1,
(id - 1) % 10000 + 1,
LPAD('', 200, '*')
FROM    generate_series(1, 500000) id;

INSERT 0 500000


WITH RECURSIVE rows AS (
  SELECT d
  FROM (
    SELECT d
    FROM t_distinct d
    ORDER BY
    glow DESC, id DESC
    LIMIT 1
  ) q
  UNION ALL
  SELECT (
    SELECT di
    FROM t_distinct di
    WHERE di.glow < (r.d).glow
    ORDER BY
    di.glow DESC, di.id DESC
    LIMIT 1
  )
  FROM rows r
  WHERE d IS NOT NULL
)
SELECT 
  (d).id, 
  (d).orderer, 
  (d).glow, 
  (d).ghigh
FROM rows
WHERE d IS NOT NULLж

ERROR:  syntax error at or near "NULLж"
LINE 28: WHERE d IS NOT NULLж
                        ^