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 dataset1
(
     id INTEGER PRIMARY KEY,
     column4 TEXT
) ;
INSERT INTO dataset1
    (id, column4)
SELECT
    i, 'column 4 for id ' || i
FROM
    generate_series(101, 150) AS s(i);
50 rows affected

CREATE TABLE dataset2
(
    column1 TEXT
) ;
INSERT INTO dataset2
    (column1)
SELECT
    'SOMETHING ' || i
FROM 
    generate_series (1001, 1020) AS s(i) ;
20 rows affected

SELECT count(DISTINCT column4) FROM dataset1 ;


count


50


CREATE FUNCTION random_column1() 
    RETURNS TEXT
    VOLATILE      -- important!
    LANGUAGE SQL
AS
$$
    SELECT
        column1
    FROM
        dataset2
    ORDER BY
        random()
    LIMIT
        1 ;
$$ ;
UPDATE
    dataset1
SET
    column4 = random_column1();
50 rows affected

SELECT count(DISTINCT column4) FROM dataset1 ;


count


19


SELECT
    column4, count(column4)
FROM
    dataset1
GROUP BY
    column4
ORDER BY
    column4 ;


column4
count


SOMETHING 1001
1

SOMETHING 1002
6

SOMETHING 1003
1

SOMETHING 1004
3

SOMETHING 1005
1

SOMETHING 1006
5

SOMETHING 1007
3

SOMETHING 1008
2

SOMETHING 1009
2

SOMETHING 1010
2

SOMETHING 1011
3

SOMETHING 1012
4

SOMETHING 1013
2

SOMETHING 1014
2

SOMETHING 1015
4

SOMETHING 1016
2

SOMETHING 1017
3

SOMETHING 1019
2

SOMETHING 1020
2

column4	count
SOMETHING 1001	1
SOMETHING 1002	6
SOMETHING 1003	1
SOMETHING 1004	3
SOMETHING 1005	1
SOMETHING 1006	5
SOMETHING 1007	3
SOMETHING 1008	2
SOMETHING 1009	2
SOMETHING 1010	2
SOMETHING 1011	3
SOMETHING 1012	4
SOMETHING 1013	2
SOMETHING 1014	2
SOMETHING 1015	4
SOMETHING 1016	2
SOMETHING 1017	3
SOMETHING 1019	2
SOMETHING 1020	2