Postgres 11

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 mytable (
    id int,
    sum int,
    keyvalue hstore
);

INSERT INTO mytable VALUES
(1,2,'"key1"=>"1","key2"=>"1"');

SELECT * FROM mytable

CREATE TABLE



INSERT 0 1




id
sum
keyvalue


1
2
"key1"=>"1", "key2"=>"1"



SELECT 1


SELECT
    *
FROM
    mytable,
    each(keyvalue)


id
sum
keyvalue
key
value


1
2
"key1"=>"1", "key2"=>"1"
key1
1

1
2
"key1"=>"1", "key2"=>"1"
key2
1



SELECT 2


    SELECT
        key,
        SUM(value::int)
    FROM (
        SELECT * FROM each('"key1"=>"1","key2"=>"1"'::hstore)
        
        UNION ALL
     
        SELECT * FROM each('"key1"=>"1","key2"=>"1","key3"=>"1"')
    ) s
    GROUP BY key


key
sum


key3
1

key1
2

key2
2



SELECT 3


SELECT
    hstore(array_agg(key), array_agg(value::text))
FROM (
    SELECT
        key,
        SUM(value::int) AS value
    FROM (
        SELECT * FROM each('"key1"=>"1","key2"=>"1"'::hstore)
        
        UNION ALL
     
        SELECT * FROM each('"key1"=>"1","key2"=>"1","key3"=>"1"')
    ) s
    GROUP BY key
) s


hstore


"key1"=>"2", "key2"=>"2", "key3"=>"1"



SELECT 1


CREATE OR REPLACE FUNCTION sum_hstore(_old hstore, _new hstore) RETURNS hstore
AS $$
DECLARE
    _out hstore;
BEGIN
        SELECT
        hstore(array_agg(key), array_agg(value::text))
    FROM (
        SELECT
            key,
            SUM(value::int) AS value
        FROM (
            SELECT * FROM each('"key1"=>"1","key2"=>"1"'::hstore)
        
            UNION ALL
     
            SELECT * FROM each('"key1"=>"1","key2"=>"1","key3"=>"1"')
        ) s
        GROUP BY key
    ) s
    INTO _out;
    
    RETURN _out;
END;
$$
LANGUAGE plpgsql ;

CREATE FUNCTION


UPDATE mytable
SET keyvalue = sum_hstore(keyvalue, '"key1"=>"1","key2"=>"1","key3"=>"1"')
WHERE id = 1;

SELECT * FROM mytable

UPDATE 1




id
sum
keyvalue


1
2
"key1"=>"2", "key2"=>"2", "key3"=>"1"



SELECT 1

id	sum	keyvalue	key	value
1	2	"key1"=>"1", "key2"=>"1"	key1	1
1	2	"key1"=>"1", "key2"=>"1"	key2	1