Postgres 9.6

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 docs(doc_text text);
CREATE TABLE tableA (
   city   text, 
   street text, 
   street_num int, 
   val_x  float, 
   val_y  float
);
    
INSERT INTO tableA (city, street, street_num, val_x, val_y)
VALUES
    ('CityA', 'Street abc', 5, 11.5, 0.5),
    ('CityA', 'Street abc', 5, 15.4, 1.8),
    ('CityC', 'Street klm', 55, 9.6, 0.8);

CREATE TABLE tableB (
   city   text, 
   street text, 
   street_num int, 
   val_x  float, 
   val_y  float
);
INSERT INTO tableB(city, street, street_num, val_x, val_y)
VALUES
    ('CityA', 'Street abc', 5, 12.4, 2.8),
    ('CityB', 'Street xyz', 18, 5.4, 1.9),
    ('CityB', 'Street xyz', 18, 8.4, 1.1);
3 rows affected

3 rows affected

-- CREATE TABLE my_result AS 
SELECT DISTINCT ON (city, street, street_num)
       city, street, street_num, val_x, val_y     -- more columns?
FROM (
   SELECT city, street, street_num, val_x, val_y  -- more columns?
   FROM   tableA
   -- WHERE  ...

   UNION ALL  -- here probably cheaper than UNION
   SELECT city, street, street_num, val_x, val_y  -- more columns?
   FROM   tableB
   -- WHERE  ...
   ) sub;


city
street
street_num
val_x
val_y


CityA
Street abc
5
11.5
0.5

CityB
Street xyz
18
5.4
1.9

CityC
Street klm
55
9.6
0.8

city	street	street_num	val_x	val_y
CityA	Street abc	5	11.5	0.5
CityB	Street xyz	18	5.4	1.9
CityC	Street klm	55	9.6	0.8