Postgres 16

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 sample (
  f1   DECIMAL(4,3),
  f2   DECIMAL(4,3)
)

CREATE TABLE


INSERT INTO
  sample
VALUES
(0.04,  0.05), 
(0.02,  0.07), 
(0.02,  0.069), 
(0.1 ,  0.1), 
(0.1 ,  0.3), 
(0.1 ,  0.4), 
(0.1 ,  0.5), 
(0.1 ,  0.6), 
(0.1 ,  0.7), 
(0.1 ,  0.8) 

INSERT 0 10


WITH
  ranked AS
(
  SELECT
    *,
    f1+f2 AS x,
    ROW_NUMBER()
      OVER (ORDER BY f1+f2, f1, f2)
    *
    1.0
    /
    COUNT(*) OVER ()
      AS percentile
  FROM
    sample
)
SELECT
  MAX(CASE WHEN x <= 0.09 THEN percentile END),
  MIN(CASE WHEN x >  0.09 THEN percentile END)
FROM
  ranked


max
min


0.30000000000000000000
0.40000000000000000000



SELECT 1


WITH
  ranked AS
(
  SELECT
    *,
    f1+f2 AS x,
    ROW_NUMBER()
      OVER (ORDER BY f1+f2, f1, f2)
    *
    1.0
    /
    COUNT(*) OVER ()
      AS percentile
  FROM
    sample
)
SELECT
  MAX(CASE WHEN percentile <= 0.3 THEN x END),
  MIN(CASE WHEN percentile >  0.3 THEN x END)
FROM
  ranked


max
min


0.090
0.200



SELECT 1