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 assets (
id serial PRIMARY KEY
, name text
, owner text
);
CREATE TABLE users (
id serial PRIMARY KEY
, username text
, ref1 text
, ref2 text
);
INSERT INTO assets (name, OWNER) VALUES
('#1', 'a')
, ('#2', 'b')
, ('#3', 'c')
, ('#4', 'a')
, ('#5', 'c')
, ('#6', 'd')
, ('#7', 'e')
, ('#8', 'd')
, ('#9', 'a')
, ('#10', 'a')
, ('#11', 'z')
;
INSERT INTO users (username, ref1, ref2) VALUES
('bobo', 'a', 'd')
, ('toto', 'b', 'e')
, ('momo', 'c', 'd')
, ('lolo', 'a', 'f')
, ('popo', 'c', 'f')
;
-- For result comparison
CREATE TABLE
CREATE TABLE
INSERT 0 11
INSERT 0 5
CREATE TABLE
INSERT 0 2
| ids | usernames | refs1 | refs2 | asset_ids | asset_count |
|---|---|---|---|---|---|
| {1,3,4,5} | {bobo,momo,lolo,popo} | {a,c} | {d,f} | {1,3,4,5,6,8} | 6 |
| {2} | {toto} | {b} | {e} | {2,7} | 2 |
SELECT 2
SHOW temp_buffers;
| temp_buffers |
|---|
| 8MB |
SHOW
CREATE OR REPLACE FUNCTION f_merged_users()
RETURNS TABLE (
ids int[] -- adapt to your actual data types !!!
, usernames text[]
, refs1 text[]
, refs2 text[]
, asset_ids int[]
, asset_count int
)
LANGUAGE plpgsql AS
$func$
BEGIN
/*
-- Set enough temp buffers, so temp tables don't spill to disk (optional)
-- NOTE: this can't be used after temporary tables have been accessed in the session
-- Using 2x size of users table. Adapt to your needs!
-- Only fires when the new size is bigger, and it has not been set manually, yet
PERFORM set_config('temp_buffers', (pg_table_size('users') * 2 / 8096)::text, true)
FROM pg_settings s
WHERE s.name = 'temp_buffers'
AND pg_table_size('users') * 2 / 8096 > s.setting::bigint
AND s.source = 'default';
*/
-- create two temp tables: one for ref1, one for ref2
-- based on the assumption that ref1 & ref2 don't overlap
CREATE TEMP TABLE r1 ON COMMIT DROP AS
SELECT ARRAY[ref1] AS r, array_agg(id) AS i
FROM (TABLE users ORDER BY id) t
GROUP BY ref1
ORDER BY ref1;
CREATE TEMP TABLE r2 ON COMMIT DROP AS
SELECT ARRAY[ref2] AS r, array_agg(id) AS i
FROM (TABLE users ORDER BY id) t
GROUP BY ref2
CREATE FUNCTION
SELECT * FROM f_merged_users();
| ids | usernames | refs1 | refs2 | asset_ids | asset_count |
|---|---|---|---|---|---|
| {1,3,4,5} | {bobo,momo,lolo,popo} | {a,c} | {d,f} | {1,3,4,5,6,8,9,10} | 8 |
| {2} | {toto} | {b} | {e} | {2,7} | 2 |
| null | null | null | {z} | {11} | 1 |
SELECT 3