Postgres 9.5

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?.

select version();


version


PostgreSQL 9.5.18 on x86_64-pc-linux-gnu (Debian 9.5.18-1.pgdg80+1), compiled by gcc (Debian 4.9.2-10+deb8u2) 4.9.2, 64-bit


CREATE TABLE zip_unique
(     store_nbr integer
     ,zipcode varchar(30)
     ,distance float);
INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521)
3 rows affected

SELECT * FROM zip_unique;


store_nbr
zipcode
distance


123
76135
2.356

456
76135
3.002

789
76135
9.521


SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
      ,DENSE_RANK() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS D_RANK
      ,RANK()       OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS R_RANK
  FROM zip_unique;


store_nbr
zipcode
distance
row_rank
d_rank
r_rank


123
76135
2.356
1
1
1

456
76135
3.002
2
2
2

789
76135
9.521
3
3
3