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 groups (
    id int,
    created_at timestamp,
    connected_lead_id int,
    on_boarded_date date,
    source text
);

INSERT INTO groups VALUES
(2, '2015-09-24 23:00:00', 8, NULL, NULL),
(4, '2015-09-25 23:00:00', 7, NULL, 'event'),
(7, '2015-09-26 23:00:00', 4, NULL, NULL),
(8, '2015-09-26 23:00:00', 2, NULL, 'referral'),
(11, '2015-09-26 23:00:00', 336, '2017-07-01', 'online'),
(142, '2016-04-27 23:00:00', 336, NULL, NULL),
(336, '2016-07-04 23:00:00', 11, '2018-09-20', 'referral');

SELECT * FROM groups;
7 rows affected



id
created_at
connected_lead_id
on_boarded_date
source


2
2015-09-24 23:00:00
8
null
null

4
2015-09-25 23:00:00
7
null
event

7
2015-09-26 23:00:00
4
null
null

8
2015-09-26 23:00:00
2
null
referral

11
2015-09-26 23:00:00
336
2017-07-01
online

142
2016-04-27 23:00:00
336
null
null

336
2016-07-04 23:00:00
11
2018-09-20
referral


CREATE OR REPLACE FUNCTION filter_groups() RETURNS int[] AS $$
DECLARE
    _seen_values int[];
    _new_values int[];
    _temprow record;
BEGIN
    FOR _temprow IN
        SELECT array_agg(id ORDER BY created_at) as ids, connected_lead_id FROM groups GROUP BY connected_lead_id ORDER BY MIN(created_at)
    LOOP
        IF array_length(_seen_values, 1) IS NULL 
            OR (_temprow.ids || _temprow.connected_lead_id) && _seen_values = FALSE THEN
            
            _new_values := _new_values || _temprow.connected_lead_id;
        END IF;
        
        _seen_values := _seen_values || _temprow.ids;
        _seen_values := _seen_values || _temprow.connected_lead_id;
    END LOOP;

    RETURN _new_values;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM filter_groups()


filter_groups


{8,7,336}


WITH RECURSIVE start_points AS (
    SELECT unnest(filter_groups()) as ids
),
filtered_groups AS (
    SELECT DISTINCT
       1 as depth,
       first_value(id) OVER w as id,
       ARRAY[(MIN(id) OVER w)] as visited,
       MIN(created_at) OVER w as created_at,
       connected_lead_id,
       MIN(on_boarded_date) OVER w as on_boarded_date,
       first_value(source) OVER w as source
    FROM groups 
    WHERE connected_lead_id IN (SELECT ids FROM start_points)
    WINDOW w AS (PARTITION BY connected_lead_id ORDER BY created_at)
    
    UNION
    
    SELECT
        fg.depth + 1,
        fg.id,
        array_append(fg.visited, g.id),
        LEAST(fg.created_at, g.created_at), 
        g.connected_lead_id, 
        LEAST(fg.on_boarded_date, g.on_boarded_date), 
        COALESCE(fg.source, g.source) 
    FROM groups g
    JOIN filtered_groups fg
    ON fg.connected_lead_id = g.id AND NOT (g.id = ANY(visited))
    
)
SELECT DISTINCT ON (id) 
    id, created_at,on_boarded_date, source 
FROM filtered_groups 
ORDER BY id, depth DESC;


id
created_at
on_boarded_date
source


2
2015-09-24 23:00:00
null
referral

4
2015-09-25 23:00:00
null
event

11
2015-09-26 23:00:00
2017-07-01
online

id	created_at	connected_lead_id	on_boarded_date	source
2	2015-09-24 23:00:00	8	null	null
4	2015-09-25 23:00:00	7	null	event
7	2015-09-26 23:00:00	4	null	null
8	2015-09-26 23:00:00	2	null	referral
11	2015-09-26 23:00:00	336	2017-07-01	online
142	2016-04-27 23:00:00	336	null	null
336	2016-07-04 23:00:00	11	2018-09-20	referral