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 T1
(deal_id INTEGER,
event_dt DATE,
exchange INTEGER,
publisher INTEGER,
user_id VARCHAR(255)
);
INSERT INTO T1 VALUES (130966 , '2017-02-27' , 3 , 31445 , 'a8155826' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'a8155826' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'a83c5806' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , '5c07500c51a4' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'a8ff5842' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , '69841690ff2a' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'ada356a' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'af95584' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b22d589a-cd9666d' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b2a4582f-bb1a45cce' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b332f43' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b3b7564f360' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b3cf295' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b6910abe' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-27' , 3 , 31445 , 'b69f5814' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-28' , 3 , 31445 , '351c5fae' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'b82757b7' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'bdef58a8' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'b840edd97b2c' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , '101543d9da32' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , '27f5-4500' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'c86a5826' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'ceb9573e' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'dab0573f' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'e4e15571' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , '3c37836e1c32' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'ef91569f' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , 'f1ef946e1075' );
1 rows affected
INSERT INTO T1 VALUES (130967 , '2017-02-28' , 3 , 31445 , '1e9b11d94184' );
1 rows affected
INSERT INTO T1 VALUES (0 , '2017-02-28' , 3 , 31446 , '1e9b11d94184' );
1 rows affected
INSERT INTO T1 VALUES (130968 , '2017-02-28' , 3 , 31446 , '1e9b11d94184' );
1 rows affected
INSERT INTO T1 VALUES (130966 , '2017-02-28' , 3 , 31445 , '1e9b11d94184' );
1 rows affected
WITH subquery as
(
SELECT
First.deal_id
,COALESCE( First.exchange, Second.exchange ) as exchange_id
,COALESCE( First.publisher, Second.publisher ) as pub_id
,COUNT (DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
,SUM (First.imps) AS First_imps
,SUM (Second.imps) AS Second_imps
FROM
(
SELECT
a.deal_id
,a.exchange
,a.publisher
,a.user_id
,1 AS imps
FROM
T1 a
WHERE
a.deal_id >= 1
) First
FULL OUTER JOIN (
SELECT
a.exchange
,a.publisher
,a.user_id
,1 AS imps
FROM
T1 a
WHERE
a.deal_id = 0
) Second
ON (
First.exchange = Second.exchange
AND First.publisher = Second.publisher
| deal_id | exchange_id | pub_id | sum | sum | sum |
|---|---|---|---|---|---|
| 130966 | 3 | 31445 | 13 | 14 | 15 |
| 130968 | 3 | 31446 | 0 | 1 | 1 |
| 130967 | 3 | 31445 | 1 | 1 | 14 |