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 IF NOT EXISTS merchant_devices
(
    id SERIAL PRIMARY KEY,
    device_id varchar(200) not null,
    pass_key varchar(200) not null,
    description varchar(400),
    blacklisted_tags TEXT[],
    blacklisted_brands TEXT[],
    active bool default false,
    created_at timestamp with time zone,
    deleted_at timestamp with time zone,
    updated_at timestamp with time zone
);
CREATE TABLE IF NOT EXISTS merchant_ads
(
    id SERIAL PRIMARY KEY,
    advertisement_id varchar(200) not null,
    ad_group varchar(200) not null,
    url varchar(200) not null,
    duration integer not null,
    name varchar(50) not null,
    type varchar(10) DEFAULT NULL,
    day_parting jsonb,
    start_date date,
    end_date date,
    tags TEXT[] not null,
    brand varchar(50),
    active boolean default false,
    ron boolean default false,
    created_at timestamp with time zone default current_timestamp,
    updated_at timestamp with time zone default current_timestamp,
    deleted_at timestamp with time zone
);
insert into merchant_devices values (1, 'device5', 'passkey', 'Device 1', '{Food, Drink}', '{McD}', true, now(), now());
1 rows affected

insert into merchant_devices values (2, 'device6', 'passkey', 'Device 2', '{Phone, Appliances}', '{LG}', true, now(), now());
1 rows affected

select * from merchant_devices;


id
device_id
pass_key
description
blacklisted_tags
blacklisted_brands
active
created_at
deleted_at
updated_at


1
device5
passkey
Device 1
{Food,Drink}
{McD}
t
2020-06-18 08:37:08.28102+01
2020-06-18 08:37:08.28102+01
null

2
device6
passkey
Device 2
{Phone,Appliances}
{LG}
t
2020-06-18 08:37:08.281904+01
2020-06-18 08:37:08.281904+01
null


select * from merchant_ads;
INSERT INTO merchant_ads (id, advertisement_id, ad_group, url, duration, name, type, day_parting, start_date, end_date, tags, brand, active, ron, created_at, updated_at, deleted_at) VALUES (10, '7f3c1307-0f82-4574-a9a3-4530b670ee02', 'cd63f0c3-1128-4227-b7e4-3454e853b321', 'https://www.url.com/ad1', 10, 'Kingfisher Beer Ad', 'video', '[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]', '2020-02-08', '2021-08-08', '{Drinks}', 'Kingfisher', true, false, '2020-06-17 23:45:04.288743 +05:30', '2020-06-17 23:45:04.288743 +05:30', null);
INSERT INTO merchant_ads (id, advertisement_id, ad_group, url, duration, name, type, day_parting, start_date, end_date, tags, brand, active, ron, created_at, updated_at, deleted_at) VALUES (12, '041df6cb-08f6-4a3b-84b7-e7d864ad3c43', 'cd63f0c3-1128-4227-b7e4-3454e853b321', 'https://www.url.com/ad2', 10, 'McD Ad', 'video', '[{"end_time": 23, "start_time": 13}]', '2020-02-08', '2021-08-08', '{Food}', 'McD', true, false, '2020-06-17 23:46:05.108803 +05:30', '2020-06-17 23:46:05.108803 +05:30', null);
INSERT INTO merchant_ads (id, advertisement_id, ad_group, url, duration, name, type, day_parting, start_date, end_date, tags, brand, active, ron, created_at, updated_at, deleted_at) VALUES (13, '5cc5a7d8-b2e1-46ed-8c8c-47501dad0f06', 'cd63f0c3-1128-4227-b7e4-3454e853b321', 'https://www.url.com/ad3', 10, 'Samsung Ad', 'video', '[{"end_time": 14, "start_time": 13}]', '2020-02-08', '2021-08-08', '{Phone, Appliances}', 'Samsung', true, false, '2020-06-17 23:45:41.765507 +05:30', '2020-06-17 23:45:41.765507 +05:30', null);
INSERT INTO merchant_ads (id, advertisement_id, ad_group, url, duration, name, type, day_parting, start_date, end_date, tags, brand, active, ron, created_at, updated_at, deleted_at) VALUES (14, 'd7fc52fd-93f0-4882-b6e7-92a62ddbc717', 'cd63f0c3-1128-4227-b7e4-3454e853b321', 'https://www.url.com/ad4', 10, 'Tesla Ad', 'video', '[{"end_time": 9, "start_time": 8}]', '2020-02-08', '2021-08-08', '{Car}', 'Tesla', true, false, '2020-06-17 23:45:41.765507 +05:30', '2020-06-17 23:45:41.765507 +05:30', null);
INSERT INTO merchant_ads (id, advertisement_id, ad_group, url, duration, name, type, day_parting, start_date, end_date, tags, brand, active, ron, created_at, updated_at, deleted_at) VALUES (15, '6db6b35c-3ff8-4991-9cc1-14a63179e5e1', 'cd63f0c3-1128-4227-b7e4-3454e853b321', 'https://www.url.com/ad5', 10, 'Burger King Ad', 'video', '[{"end_time": 16, "start_time": 8}]', '2020-02-08', '2021-08-08', '{Food, Drinks}', 'Burger King', true, false, '2020-06-17 23:45:41.765507 +05:30', '2020-06-17 23:45:41.765507 +05:30', null);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from merchant_ads;


id
advertisement_id
ad_group
url
duration
name
type
day_parting
start_date
end_date
tags
brand
active
ron
created_at
updated_at
deleted_at


10
7f3c1307-0f82-4574-a9a3-4530b670ee02
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad1
10
Kingfisher Beer Ad
video
[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]
2020-02-08
2021-08-08
{Drinks}
Kingfisher
t
f
2020-06-17 19:15:04.288743+01
2020-06-17 19:15:04.288743+01
null

12
041df6cb-08f6-4a3b-84b7-e7d864ad3c43
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad2
10
McD Ad
video
[{"end_time": 23, "start_time": 13}]
2020-02-08
2021-08-08
{Food}
McD
t
f
2020-06-17 19:16:05.108803+01
2020-06-17 19:16:05.108803+01
null

13
5cc5a7d8-b2e1-46ed-8c8c-47501dad0f06
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad3
10
Samsung Ad
video
[{"end_time": 14, "start_time": 13}]
2020-02-08
2021-08-08
{Phone,Appliances}
Samsung
t
f
2020-06-17 19:15:41.765507+01
2020-06-17 19:15:41.765507+01
null

14
d7fc52fd-93f0-4882-b6e7-92a62ddbc717
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad4
10
Tesla Ad
video
[{"end_time": 9, "start_time": 8}]
2020-02-08
2021-08-08
{Car}
Tesla
t
f
2020-06-17 19:15:41.765507+01
2020-06-17 19:15:41.765507+01
null

15
6db6b35c-3ff8-4991-9cc1-14a63179e5e1
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad5
10
Burger King Ad
video
[{"end_time": 16, "start_time": 8}]
2020-02-08
2021-08-08
{Food,Drinks}
Burger King
t
f
2020-06-17 19:15:41.765507+01
2020-06-17 19:15:41.765507+01
null


INSERT INTO merchant_devices (id, device_id, pass_key, description, blacklisted_tags, blacklisted_brands, active, created_at, deleted_at, updated_at) VALUES (7, 'device1', 'pass_key', 'Test Device 1', '{Food,Beverage,Drinks}', '{Burger King,Pizza Hut}', true, '2020-06-17 15:47:34.489214 +05:30', null, '2020-06-17 15:47:34.489214 +05:30');
INSERT INTO merchant_devices (id, device_id, pass_key, description, blacklisted_tags, blacklisted_brands, active, created_at, deleted_at, updated_at) VALUES (9, 'device3', 'pass_key', 'On the Street', '{}', '{}', true, '2020-06-17 15:50:32.513050 +05:30', null, '2020-06-17 15:50:32.513050 +05:30');
INSERT INTO merchant_devices (id, device_id, pass_key, description, blacklisted_tags, blacklisted_brands, active, created_at, deleted_at, updated_at) VALUES (8, 'device2', 'pass_key', 'At McD', '{Food,Drinks}', '{Burger King,Pizza Hut}', true, '2020-06-17 15:50:06.212227 +05:30', null, '2020-06-17 15:50:06.212227 +05:30');
INSERT INTO merchant_devices (id, device_id, pass_key, description, blacklisted_tags, blacklisted_brands, active, created_at, deleted_at, updated_at) VALUES (10, 'device4', 'pass_key', 'On the Street', '{Food}', '{}', true, '2020-06-17 15:50:56.047493 +05:30', null, '2020-06-17 15:50:56.047493 +05:30');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from merchant_devices;


id
device_id
pass_key
description
blacklisted_tags
blacklisted_brands
active
created_at
deleted_at
updated_at


1
device5
passkey
Device 1
{Food,Drink}
{McD}
t
2020-06-18 08:37:08.28102+01
2020-06-18 08:37:08.28102+01
null

2
device6
passkey
Device 2
{Phone,Appliances}
{LG}
t
2020-06-18 08:37:08.281904+01
2020-06-18 08:37:08.281904+01
null

7
device1
pass_key
Test Device 1
{Food,Beverage,Drinks}
{"Burger King","Pizza Hut"}
t
2020-06-17 11:17:34.489214+01
null
2020-06-17 11:17:34.489214+01

9
device3
pass_key
On the Street
{}
{}
t
2020-06-17 11:20:32.51305+01
null
2020-06-17 11:20:32.51305+01

8
device2
pass_key
At McD
{Food,Drinks}
{"Burger King","Pizza Hut"}
t
2020-06-17 11:20:06.212227+01
null
2020-06-17 11:20:06.212227+01

10
device4
pass_key
On the Street
{Food}
{}
t
2020-06-17 11:20:56.047493+01
null
2020-06-17 11:20:56.047493+01


select a.* from merchant_ads a join merchant_devices d on not (d.blacklisted_tags && a.tags) and not (d.blacklisted_brands && array[a.brand::text]), jsonb_array_elements(a.day_parting) where d.device_id = 'device6' and a.active = true and (date(now()) BETWEEN a.start_date AND a.end_date);


id
advertisement_id
ad_group
url
duration
name
type
day_parting
start_date
end_date
tags
brand
active
ron
created_at
updated_at
deleted_at


10
7f3c1307-0f82-4574-a9a3-4530b670ee02
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad1
10
Kingfisher Beer Ad
video
[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]
2020-02-08
2021-08-08
{Drinks}
Kingfisher
t
f
2020-06-17 19:15:04.288743+01
2020-06-17 19:15:04.288743+01
null

10
7f3c1307-0f82-4574-a9a3-4530b670ee02
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad1
10
Kingfisher Beer Ad
video
[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]
2020-02-08
2021-08-08
{Drinks}
Kingfisher
t
f
2020-06-17 19:15:04.288743+01
2020-06-17 19:15:04.288743+01
null

12
041df6cb-08f6-4a3b-84b7-e7d864ad3c43
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad2
10
McD Ad
video
[{"end_time": 23, "start_time": 13}]
2020-02-08
2021-08-08
{Food}
McD
t
f
2020-06-17 19:16:05.108803+01
2020-06-17 19:16:05.108803+01
null

14
d7fc52fd-93f0-4882-b6e7-92a62ddbc717
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad4
10
Tesla Ad
video
[{"end_time": 9, "start_time": 8}]
2020-02-08
2021-08-08
{Car}
Tesla
t
f
2020-06-17 19:15:41.765507+01
2020-06-17 19:15:41.765507+01
null

15
6db6b35c-3ff8-4991-9cc1-14a63179e5e1
cd63f0c3-1128-4227-b7e4-3454e853b321
https://www.url.com/ad5
10
Burger King Ad
video
[{"end_time": 16, "start_time": 8}]
2020-02-08
2021-08-08
{Food,Drinks}
Burger King
t
f
2020-06-17 19:15:41.765507+01
2020-06-17 19:15:41.765507+01
null

id	device_id	pass_key	description	blacklisted_tags	blacklisted_brands	active	created_at	deleted_at	updated_at
1	device5	passkey	Device 1	{Food,Drink}	{McD}	t	2020-06-18 08:37:08.28102+01	2020-06-18 08:37:08.28102+01	null
2	device6	passkey	Device 2	{Phone,Appliances}	{LG}	t	2020-06-18 08:37:08.281904+01	2020-06-18 08:37:08.281904+01	null

id	advertisement_id	ad_group	url	duration	name	type	day_parting	start_date	end_date	tags	brand	active	ron	created_at	updated_at	deleted_at
10	7f3c1307-0f82-4574-a9a3-4530b670ee02	cd63f0c3-1128-4227-b7e4-3454e853b321	https://www.url.com/ad1	10	Kingfisher Beer Ad	video	[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]	2020-02-08	2021-08-08	{Drinks}	Kingfisher	t	f	2020-06-17 19:15:04.288743+01	2020-06-17 19:15:04.288743+01	null
12	041df6cb-08f6-4a3b-84b7-e7d864ad3c43	cd63f0c3-1128-4227-b7e4-3454e853b321	https://www.url.com/ad2	10	McD Ad	video	[{"end_time": 23, "start_time": 13}]	2020-02-08	2021-08-08	{Food}	McD	t	f	2020-06-17 19:16:05.108803+01	2020-06-17 19:16:05.108803+01	null
13	5cc5a7d8-b2e1-46ed-8c8c-47501dad0f06	cd63f0c3-1128-4227-b7e4-3454e853b321	https://www.url.com/ad3	10	Samsung Ad	video	[{"end_time": 14, "start_time": 13}]	2020-02-08	2021-08-08	{Phone,Appliances}	Samsung	t	f	2020-06-17 19:15:41.765507+01	2020-06-17 19:15:41.765507+01	null
14	d7fc52fd-93f0-4882-b6e7-92a62ddbc717	cd63f0c3-1128-4227-b7e4-3454e853b321	https://www.url.com/ad4	10	Tesla Ad	video	[{"end_time": 9, "start_time": 8}]	2020-02-08	2021-08-08	{Car}	Tesla	t	f	2020-06-17 19:15:41.765507+01	2020-06-17 19:15:41.765507+01	null
15	6db6b35c-3ff8-4991-9cc1-14a63179e5e1	cd63f0c3-1128-4227-b7e4-3454e853b321	https://www.url.com/ad5	10	Burger King Ad	video	[{"end_time": 16, "start_time": 8}]	2020-02-08	2021-08-08	{Food,Drinks}	Burger King	t	f	2020-06-17 19:15:41.765507+01	2020-06-17 19:15:41.765507+01	null