Postgres 12

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?.
select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE public.users (
	id serial NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	firstname text NOT NULL,
	lastname text NOT NULL,
	username text NOT NULL,
	"password" text NOT NULL,
	country_id int4 NULL,
	confirmed_at timestamptz NULL,
	email text NOT NULL,
	reset_token text NULL,
	token_expires_at timestamp NULL,
	api_id text NULL,
	api_key text NULL,
	is_admin bool NOT NULL DEFAULT false,
	last_login_at timestamptz NULL,
	payment_method int4 NULL DEFAULT 0,
	encrypted_password varchar NULL DEFAULT ''::character varying,
	sign_in_count int4 NOT NULL DEFAULT 0,
	last_sign_in_at timestamp NULL,
	last_sign_in_ip inet NULL,
	referral_url varchar(255) NULL,
	company_id int8 NULL,
	linkedin_url text NULL,
	twitter_url text NULL,
	telephone text NULL,
	persona text NULL,
	zoho_id varchar(255) NULL,
	session_count int4 NULL DEFAULT 0,
	last_seen_at timestamptz NULL,
	last_session_started_at timestamptz NULL,
	CONSTRAINT pk_users PRIMARY KEY (id)
);
CREATE TABLE public.cameras (
	id serial NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	exid text NOT NULL,
	owner_id int4 NOT NULL,
	is_public bool NOT NULL,
	config json NOT NULL,
	name text NOT NULL,
	last_polled_at timestamptz NULL DEFAULT now(),
	is_online bool NULL,
	timezone text NULL,
	last_online_at timestamptz NULL DEFAULT now(),
	"location" geography NULL,
	mac_address macaddr NULL,
	model_id int4 NULL,
	discoverable bool NOT NULL DEFAULT false,
	thumbnail_url text NULL,
	is_online_email_owner_notification bool NOT NULL DEFAULT false,
	alert_emails text NULL,
	offline_reason varchar(255) NULL,
	project_id int8 NULL,
	sim_id int8 NULL,
	ptz bool NOT NULL DEFAULT false,
	channel int4 NULL,
	CONSTRAINT pk_streams PRIMARY KEY (id)
);
CREATE UNIQUE INDEX cameras_exid_index ON public.cameras USING btree (exid);
CREATE INDEX cameras_mac_address_index ON public.cameras USING btree (mac_address);
CREATE INDEX ix_streams_owner_id ON public.cameras USING btree (owner_id);



ALTER TABLE public.cameras ADD CONSTRAINT cameras_owner_id_fkey 
  FOREIGN KEY (owner_id) REFERENCES users(id);
CREATE TABLE public.archives (
	id serial NOT NULL,
	camera_id int4 NOT NULL,
	exid text NOT NULL,
	title text NOT NULL,
	from_date timestamptz NOT NULL,
	to_date timestamptz NOT NULL,
	status int4 NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	requested_by int4 NOT NULL,
	embed_time bool NULL,
	public bool NULL,
	frames int4 NULL DEFAULT 0,
	url varchar(255) NULL,
	file_name varchar(255) NULL,
	"type" varchar(255) NULL,
	error_message text NULL,
	CONSTRAINT archives_pkey PRIMARY KEY (id)
);



ALTER TABLE public.archives 
  ADD CONSTRAINT archives_camera_id_fkey 
    FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.archives 
  ADD CONSTRAINT archives_requested_by_fkey 
    FOREIGN KEY (requested_by) REFERENCES users(id);
CREATE TABLE public.timelapses (
	id serial NOT NULL,
	camera_id int4 NOT NULL,
	exid varchar(255) NOT NULL,
	title varchar(255) NOT NULL,
	frequency int4 NOT NULL,
	snapshot_count int4 NULL DEFAULT 0,
	status int4 NOT NULL,
	date_always bool NULL DEFAULT false,
	from_datetime timestamp NULL,
	time_always bool NULL DEFAULT false,
	to_datetime timestamp NULL,
	watermark_logo text NULL,
	watermark_position varchar(255) NULL,
	inserted_at timestamp NOT NULL,
	updated_at timestamp NOT NULL,
	user_id int4 NOT NULL,
	extra json NULL,
	CONSTRAINT timelapses_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX timelapse_exid_unique_index ON public.timelapses USING btree (exid);


ALTER TABLE public.timelapses 
  ADD CONSTRAINT timelapses_camera_id_fkey 
    FOREIGN KEY (camera_id) REFERENCES cameras(id);

ALTER TABLE public.timelapses 
  ADD CONSTRAINT timelapses_user_id_fkey 
    FOREIGN KEY (user_id) REFERENCES users(id);
CREATE TABLE public.compares (
	id serial NOT NULL,
	exid varchar(255) NOT NULL,
	name varchar(255) NOT NULL,
	before_date timestamp NOT NULL,
	after_date timestamp NOT NULL,
	embed_code varchar(255) NOT NULL,
	camera_id int4 NOT NULL,
	inserted_at timestamp NOT NULL,
	updated_at timestamp NOT NULL,
	create_animation bool NULL DEFAULT false,
	status int4 NOT NULL DEFAULT 0,
	requested_by int4 NOT NULL,
	public bool NULL DEFAULT true,
	CONSTRAINT compares_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX compare_exid_unique_index ON public.compares USING btree (exid);


ALTER TABLE public.compares 
  ADD CONSTRAINT compares_camera_id_fkey 
    FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.compares 
  ADD CONSTRAINT compares_requested_by_fkey 
   FOREIGN KEY (requested_by) REFERENCES users(id);
INSERT INTO public.users (created_at,updated_at,firstname,lastname,username,"password",country_id,confirmed_at,email,reset_token,token_expires_at,api_id,api_key,is_admin,last_login_at,payment_method,encrypted_password,sign_in_count,last_sign_in_at,last_sign_in_ip,referral_url,company_id,linkedin_url,twitter_url,telephone,persona,zoho_id,session_count,last_seen_at,last_session_started_at) VALUES
	 ('2017-10-16 15:32:14.150934+05','2020-12-15 23:05:47.77491+05','FRANCESCO','INTOCCIA','fintocci','$2a$12$xIv',100,'2017-10-16 15:32:13+05','fintocci@its.jnj.com',NULL,NULL,'8b088a57','5854c21934544d70b5286d7eb0868876',false,'2018-05-04 21:22:11.992139+05',2,'jLnAKVO',0,NULL,NULL,NULL,2907,'https://www.linkedin.com/in/francesco-intoccia-6379431b',NULL,NULL,NULL,'432169000008650488',9,NULL,NULL),
	 ('2020-10-28 17:41:08.692263+05','2021-01-07 21:39:38.464816+05','Reuben','Kirrane','rkirrane@deniswilliams.ie','$2b$12$erwTBqq5fk9Vp',100,'2020-10-28 17:41:08.479745+05','rkirrane@deniswilliams.ie',NULL,'2020-11-30 09:53:53.671081','cbc9e072','263ea96c0bb54bbfac869f82b0021f62',false,'2021-01-07 21:39:37.184381+05',0,'',4,NULL,'86.45.26.197'::inet,'fionn@evercam.io shared camera cgdmc-piqer',3865,NULL,NULL,'+353860294681','HQ','432169000051419055',5,'2021-01-07 21:39:38.436134+05','2021-01-07 21:39:38.436134+05'),
	 ('2020-03-26 22:55:47.901869+05','2021-04-19 14:39:11.327593+05','Bartosz','Borowiak','b.borowiak@waterman-moylan.ie','$2b$12$LvXTrKK',100,'2020-03-26 22:55:47.696433+05','b.borowiak@waterman-moylan.ie',NULL,NULL,'6a7f437a','943e0653a4234acdbf1cf20d8b3f4ae5',false,'2021-04-19 14:39:06.116871+05',0,'',19,NULL,'188.141.50.10'::inet,'construction@evercam.io shared camera irish-tiera',2797,NULL,NULL,NULL,'HQ','432169000036041083',49,'2021-04-19 14:39:11.327482+05','2021-04-19 14:39:11.327482+05'),
	 ('2018-05-17 16:42:28.225984+05','2020-12-15 23:05:36.415438+05','Paul','Braham','paul.braham@hse.ie','$2a$10$U.U',100,'2018-05-17 16:42:27+05','paul.braham@hse.ie','3e9a7089ffc8f31a0ed2ba6a1bf88259','2018-05-19 09:17:36.860184','3947ce36','ce3bb16fb1bd4e4cb32b5288182fadcf',false,'2018-06-08 18:40:03.912919+05',2,'$2a$10$.lEeMFK',0,NULL,NULL,NULL,2665,'https://www.linkedin.com/in/paul-braham-3b5a6795',NULL,NULL,NULL,'432169000009677055',5,NULL,NULL),
	 ('2018-07-05 14:49:47.797514+05','2020-12-15 23:05:33.503511+05','Liam','Rahilly','lrahilly@meicltd.com','$2a$12$KBNDJBBdUDVOjcBqErU',100,'2018-07-05 14:49:47+05','lrahilly@meicltd.com',NULL,NULL,'4d380adf','28b1a2c4f8824f87b527927e4afbb559',false,'2018-07-05 19:04:45.98841+05',2,'$2a$/y4WJXhKRnX1O',0,NULL,NULL,NULL,2842,NULL,NULL,NULL,NULL,'432169000020553441',2,NULL,NULL),
	 ('2018-06-07 21:01:59.802852+05','2021-01-25 22:36:45.697583+05','Ricky','Fretwell','rick.fretwell@gmail.com','$2a$12$3Cz9kZvKZyo9RTe',75,'2018-06-07 21:01:59+05','rick.fretwell@gmail.com',NULL,NULL,'3016bd04','93b944226c77415c80c63dadb31f8b55',false,'2018-10-12 18:58:53.005926+05',2,'$2asgdsgds',0,NULL,NULL,NULL,3011,NULL,NULL,NULL,'PM','432169000009123036',15,NULL,NULL),
	 ('2018-09-11 14:33:32.681788+05','2021-01-13 16:50:52.429029+05','Site Manager','DHL','sandgate@developmenthouseltd.co.uk','$2a$10$wra',75,'2018-09-11 14:33:32+05','sandgate@developmenthouseltd.co.uk',NULL,NULL,'26699043','bab8d1e53a95494984c2790c225bb207',false,'2018-12-04 19:35:12.373132+05',2,'$2a$10$w',2,'2019-01-28 07:49:22.924148','212.140.202.116'::inet,NULL,2645,NULL,NULL,NULL,'PM','432169000013651003',5,NULL,NULL),
	 ('2018-05-25 15:18:47.255188+05','2021-02-09 21:57:09.810436+05','tommy','macken','tmacken@rhatiganohl.com','$2a$12$8mR9GQ3r2iJcted6kOg',100,'2018-05-25 15:18:47+05','tmacken@rhatiganohl.com',NULL,NULL,'7f12fcb2','83cf65a01d4b495d91165ad2b605fd45',false,'2021-02-09 21:57:09.810267+05',2,'$2a$12$8mR9GQ3r.cD1/',1,NULL,'89.100.126.110'::inet,NULL,2834,NULL,NULL,NULL,NULL,'432169000007088001',3,NULL,NULL),
	 ('2018-09-27 16:08:41.721788+05','2020-12-15 23:05:27.060385+05','Henry','Scanlon','hscanlon@johnpaul.ie','$2b$12$',100,'2018-09-27 16:08:41+05','hscanlon@johnpaul.ie','3d4f89d42235416693db9149dff4bc09','2020-12-05 23:50:17.877408','c05d6a81','dd8367b40efd40f78ff0598fcfa249e4',false,'2020-12-05 04:51:04.298163+05',2,'$2a$12.',3,NULL,'89.19.67.31'::inet,NULL,2541,NULL,NULL,NULL,'PM','432169000020565181',4,NULL,NULL),
	 ('2021-03-01 20:22:04.236702+05','2021-03-01 20:22:08.816447+05','Daniela','Enkelmann','denkelmann@kohlerronan.com','$2b$',228,'2021-03-01 20:22:04.009769+05','denkelmann@kohlerronan.com',NULL,NULL,'4a840732','011cf8035f56419f955bd4db7be802f6',false,'2021-03-01 20:22:05.091482+05',0,'',0,NULL,'69.195.32.10'::inet,'construction@evercam.io shared camera millr-psbjc',4116,NULL,NULL,NULL,'HQ','432169000063554073',1,'2021-03-01 20:22:06.213188+05','2021-03-01 20:22:06.213188+05');
10 rows affected

INSERT INTO public.cameras (created_at,updated_at,exid,owner_id,is_public,config,"name",last_polled_at,is_online,timezone,last_online_at,"location",mac_address,model_id,discoverable,thumbnail_url,is_online_email_owner_notification,alert_emails,offline_reason,project_id,sim_id,ptz,channel) VALUES
	 ('2021-04-21 16:42:27.363367+05','2021-04-21 16:43:20.770069+05','siskm-guhev',1,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsd.io","external_http_port":"50204","external_rtsp_port":"50202","nvr_http_port":"50201","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Sisk Mater 1','2021-04-21 13:46:29.405674+05',NULL,'Europe/Dublin','2021-04-21 13:46:29.405715+05','SRID=4326;POINT (-6.265669 53.3598586)'::geography,NULL,3318,false,NULL,false,NULL,NULL,720,366,false,101),
	 ('2021-02-09 19:00:04.038143+05','2021-04-19 11:06:28.755652+05','stens-wjcyf',2,false,'{"auth":{"basic":{"password":"Merlin53","username":"admin"}},"external_host":"dsdfs.io","external_http_port":"20804","external_rtsp_port":"20802","nvr_http_port":"20801","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Stenson Court','2021-03-11 13:33:36+05',NULL,'Europe/London','2021-03-11 12:45:22+05','SRID=4326;POINT (-1.1541171 53.5098615)'::geography,NULL,3318,false,NULL,false,NULL,NULL,580,673,false,101),
	 ('2021-03-23 01:47:53.639515+05','2021-04-19 11:06:29.300114+05','dslrf-bxtgj',3,false,'{"auth":{"basic":{"password":"afz9XKqYLuExCKZu","username":"evercam_admin"}},"external_host":"dsd.io","external_http_port":"80","external_rtsp_port":"1111","nvr_http_port":"1111","urls":{"jpg":"evercam.io"}}','DSLR FTP','2021-03-22 15:55:00.648167+05',NULL,'Etc/UTC','2021-03-22 15:55:00.648188+05','SRID=4326;POINT (0 0)'::geography,NULL,151,false,NULL,false,NULL,NULL,211,573,false,NULL),
	 ('2021-02-12 18:56:18.812258+05','2021-04-19 11:06:29.205915+05','norma-cfnbk',4,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"185.233.176","external_http_port":"11079","external_rtsp_port":"11080","nvr_http_port":"11079","urls":{"h264":"/ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture?videoResolutionWidth=1920&videoResolutionHeight=1080"}}','Normal Test Camera','2021-03-16 13:10:33+05',NULL,'Europe/Dublin','2021-03-16 13:10:13+05','SRID=4326;POINT (0 0)'::geography,NULL,3317,false,NULL,false,NULL,NULL,586,NULL,false,101),
	 ('2021-03-15 19:49:35.166558+05','2021-04-19 11:06:28.82161+05','jubil-tjlmk',5,false,'{"auth":{"basic":{"password":"Permanex1","username":"admin"}},"external_host":"dsd.io","external_http_port":"20839","external_rtsp_port":"20837","nvr_http_port":"20836","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Jubilee Phase 2','2021-03-15 13:55:43.355517+05',NULL,'Europe/London','2021-03-15 13:55:43.355538+05','SRID=4326;POINT (-0.0866958 51.50490790000001)'::geography,NULL,3318,false,NULL,false,NULL,NULL,656,705,false,101),
	 ('2019-11-01 18:54:31.367507+05','2021-04-19 11:06:28.884481+05','cairn-fegtm',6,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"citywest.dyndns","external_http_port":"65001","external_rtsp_port":"9100","nvr_http_port":"8100","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Cairn Homes Citywest','2020-11-11 21:38:38+05',NULL,'Europe/Dublin','2020-11-11 21:38:38+05','SRID=4326;POINT (-6.4285886 53.2849647)'::geography,NULL,3318,false,NULL,false,NULL,'nxdomain',154,NULL,false,101),
	 ('2019-08-06 17:25:35.801228+05','2021-04-19 11:06:29.635423+05','colog-ywtlq',7,false,'{"auth":{"basic":{"password":"DT$8nK!86","username":"tw"}},"external_host":"220.35","external_http_port":"8001","external_rtsp_port":"9001","nvr_http_port":"","urls":{"h264":"axis-media/media.amp?resolution=800x600","jpg":"axis-cgi/jpg/image.cgi"}}','Cologne 1','2021-04-07 15:14:11+05',true,'Europe/Berlin','2021-04-07 15:13:11+05','SRID=4326;POINT (6.909336 50.9484714)'::geography,NULL,4,false,NULL,false,NULL,NULL,NULL,NULL,false,NULL),
	 ('2019-07-08 18:23:24.593418+05','2021-04-19 11:06:28.943171+05','graha-wkiaj',8,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsdsd.io","external_http_port":"20254","external_rtsp_port":"20252","nvr_http_port":"20251","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Graham Drumaroad','2021-02-27 01:42:09+05',true,'Europe/Dublin','2021-02-27 01:41:18+05','SRID=4326;POINT (-5.8936402 54.3329881)'::geography,'4c:bd:8f:26:f0:16'::macaddr,3318,false,NULL,false,NULL,NULL,249,234,false,101),
	 ('2021-01-15 21:29:09.642408+05','2021-04-19 11:06:29.419438+05','polop-pcsdm',9,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"185.176.121","external_http_port":"11190","urls":{"h264":"/ISAPI/Streaming/channels/2401/av_stream","jpg":"ISAPI/Streaming/channels/2401/picture?videoResolutionWidth=1920&videoResolutionHeight=1080"}}','Polo Parking','2021-01-18 11:39:01+05',NULL,'Europe/Dublin','2021-01-18 11:38:37+05','SRID=4326;POINT (0 0)'::geography,NULL,3317,false,NULL,false,NULL,NULL,17,NULL,false,2401),
	 ('2021-04-20 19:21:10.799916+05','2021-04-21 11:09:08.369512+05','bmdda-dzlfx',10,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsdsd.io","external_http_port":"51104","external_rtsp_port":"50102","nvr_http_port":"50101","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','BMD Davitt Rd TC1-1','2021-04-20 13:58:35.571222+05',NULL,'Europe/London','2021-04-20 13:58:35.571258+05','SRID=4326;POINT (-6.3155058 53.3355761)'::geography,NULL,3318,false,NULL,false,NULL,NULL,719,409,false,101);
10 rows affected

INSERT INTO public.timelapses (camera_id,exid,title,frequency,snapshot_count,status,date_always,from_datetime,time_always,to_datetime,watermark_logo,watermark_position,inserted_at,updated_at,user_id,extra) VALUES
	 (1,'longp-mvxla','Long Ponds',60,6767,3,true,'2016-08-29 00:00:00',true,'2016-08-29 23:59:59','','TopLeft','2017-05-18 08:16:13','2017-07-13 04:45:45.174592',2,'{"id":798}'),
	 (2,'basem-hfmjp','Basement Today',15,360,6,true,'2019-01-22 10:00:00',true,'2019-01-22 11:00:00','none',NULL,'2019-01-22 11:49:46.333172','2019-01-22 11:49:46.333183',3,NULL),
	 (3,'3pp-cloxd','3PP',30,7982,3,true,'1900-01-01 08:00:00',false,'8888-12-31 19:00:00','','TopLeft','2017-05-18 08:16:12','2017-07-12 17:25:45.413265',4,'{"id":717}'),
	 (4,'digit-rgblx','Digital HUb',15,6804,3,true,'1900-01-01 06:00:00',false,'8888-12-31 19:00:00','','TopLeft','2017-05-18 08:16:10','2017-05-18 08:16:10',1,'{"id":260}'),
	 (5,'demo-jvnzg','Demo',360,768,3,true,'1900-01-01 00:00:00',true,'8888-12-31 23:59:59','','TopLeft','2017-05-18 08:16:10','2017-05-18 08:16:10',2,'{"id":559}');
5 rows affected

INSERT INTO public.compares (exid,"name",before_date,after_date,embed_code,camera_id,inserted_at,updated_at,create_animation,status,requested_by,public) VALUES
	 ('compa-ylxwctg','Compare: 8th Sep to 29th Oct','2017-09-08 14:35:25','2018-10-29 05:51:47','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''iaaco-auftq 2017-09-08-14_35_25 2018-10-29-05_51_47 compa-ylxwctg autoplay''></script>',1,'2018-10-29 05:52:09.659566','2018-10-29 05:52:15.391303',true,1,2,true),
	 ('test-hroqe','test ','2017-06-21 13:20:55','2018-01-17 10:27:58','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''point-iytnz 2017-06-21-13_20_55 2018-01-17-10_27_58 test-hroqe autoplay''></script>',2,'2018-01-17 11:08:19.845168','2018-04-03 05:19:54.360234',true,1,3,true),
	 ('compa-ltafnon','Compare: 26th Jul to 29th Oct','2018-07-26 14:23:00','2018-10-29 05:37:00','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''crome-tidcz 2018-07-26-14_23_00 2018-10-29-05_37_00 compa-ltafnon autoplay''></script>',3,'2018-10-29 05:53:39.989565','2018-10-29 05:53:44.78214',true,1,2,true),
	 ('compa-tjhacxd','Compare: 23rd Jan to 27th Oct','2018-01-23 13:37:55','2018-10-27 12:07:22','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''scotc-rpfbq 2018-01-23-13_37_55 2018-10-27-12_07_22 compa-tjhacxd autoplay''></script>',1,'2018-10-29 05:56:52.67979','2018-10-29 05:56:58.646538',true,1,2,true),
	 ('compa-vztxtjc','Compare: 18th Jul to 1st Nov','2015-07-18 15:07:00','2018-11-01 00:05:43','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''bennett 2015-07-18-15_07_00 2018-11-01-00_05_43 compa-vztxtjc autoplay''></script>',2,'2018-11-15 10:03:24.85408','2018-11-15 10:03:33.140659',true,1,4,true),
	 ('compa-momncjm','Compare: 11th Oct to 19th Nov','2018-10-11 11:10:51','2018-11-19 15:26:56','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''leins-ugxco 2018-10-11-11_10_51 2018-11-19-15_26_56 compa-momncjm autoplay''></script>',1,'2018-11-19 15:28:52.359459','2018-11-19 15:29:04.35427',true,1,1,true),
	 ('compa-cockhyp','Compare: 11th Jan to 19th Dec','2018-01-11 14:36:19','2018-12-19 14:12:09','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''mscpi-pkjwh 2018-01-11-14_36_19 2018-12-19-14_12_09 compa-cockhyp autoplay''></script>',4,'2018-12-19 14:13:35.856163','2018-12-19 14:13:42.713771',true,1,5,true),
	 ('itsac-ihtlze','Its a compare','2016-03-19 05:21:12','2020-01-01 08:45:34','<div id=''evercam-compare''></div><script src=''https://dash2.evercam.io/evercam_compare.js'' class=''gpocam 2016-03-19-05_21_12 2020-01-01-08_45_34 itsac-ihtlze autoplay''></script>',4,'2020-01-01 08:46:23.172885','2020-01-01 08:46:28.088856',true,1,1,true),
	 ('compa-kycgmav','Compare: 1st Nov to 5th Apr','2018-11-01 10:11:04','2019-04-05 11:04:02','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''south-ragcn 2018-11-01-10_11_04 2019-04-05-11_04_02 compa-kycgmav autoplay''></script>',1,'2019-04-08 14:54:03.809314','2019-04-08 14:54:10.807723',true,1,2,true),
	 ('compa-kdxjbhh','Compare: 4th Dec to 14th Dec','2018-12-04 16:30:03','2018-12-14 16:12:04','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''still-bojft 2018-12-04-16_30_03 2018-12-14-16_12_04 compa-kdxjbhh autoplay''></script>',2,'2018-12-14 22:25:00.735079','2018-12-14 22:25:08.440506',true,1,4,true);
10 rows affected

INSERT INTO public.archives (camera_id,exid,title,from_date,to_date,status,created_at,requested_by,embed_time,public,frames,url,file_name,"type",error_message) VALUES
	 (1,'concre-r6ys','Concrete Pour','2017-04-05 18:25:05+05','2017-04-05 18:55:05+05',2,'2017-04-05 19:46:38+05',1,false,false,840,NULL,NULL,'clip',NULL),
	 (1,'201809-27xn','2018-09-26T10:00:04.000Z','2018-09-26 14:00:04+05','2018-09-26 14:00:04+05',2,'2018-11-27 21:05:01.813853+05',1,NULL,NULL,0,NULL,'201809-27xn.jpeg','edit',NULL),
	 (4,'201902-htu9','2019-02-01T10:13:47.000Z','2019-02-01 15:13:47+05','2019-02-01 15:13:47+05',2,'2019-02-01 15:14:56+05',1,NULL,NULL,0,NULL,'201902-htu9.jpeg','edit',NULL),
	 (5,'extrac-png8','Extract','2017-04-12 21:25:00+05','2017-04-12 21:40:00+05',2,'2017-04-18 13:50:42+05',5,true,false,853,NULL,NULL,'clip',NULL),
	 (2,'joecli-kvpw','joe clip','2017-04-19 20:10:00+05','2017-04-19 20:40:00+05',2,'2017-04-20 04:02:35+05',1,NULL,NULL,799,NULL,NULL,'clip',NULL),
	 (3,'joescl-pgfl','Joe''s Clip','2017-04-19 20:40:00+05','2017-04-19 21:10:00+05',2,'2017-04-21 14:23:47+05',4,NULL,NULL,542,NULL,NULL,'clip',NULL),
	 (3,'bartra-bnm5','Bartra - Loughshinny Time-lapse, October 2018','2018-10-15 17:49:52+05','2018-10-15 17:49:52+05',2,'2018-10-15 18:49:51.783621+05',1,NULL,NULL,0,'https://vimeo.com/294316497',NULL,'url',NULL),
	 (5,'joescl-fv4u','Joe''s clip','2017-04-19 20:10:00+05','2017-04-19 20:40:00+05',2,'2017-04-21 14:25:13+05',4,NULL,NULL,799,NULL,NULL,'clip',NULL),
	 (1,'spark-kyzrq','Sparks Building, Newcastle - Time-lapse, July 2020','2020-07-28 20:25:33.449425+05','2020-07-28 20:25:33.449425+05',2,'2020-07-28 20:25:33.449623+05',1,NULL,NULL,0,'https://vimeo.com/442398703/ec9450b8e0',NULL,'url',NULL),
	 (2,'201611-luw8','2016-11-06 BWLiffeyCruises 1200-1230','2016-11-06 17:00:00+05','2016-11-06 17:30:00+05',2,'2016-11-23 14:36:32+05',4,false,false,897,NULL,NULL,'clip',NULL);
10 rows affected

-- select * from users;
-- select * from cameras
-- select * from compares
-- select * from archives
-- select * from timelapses
WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;


tab_name
id
exid
title
camera_id
requested_by


Archives
1
concre-r6ys
Concrete P
1
1

Archives
2
201809-27xn
2018-09-26
1
1

Archives
3
201902-htu9
2019-02-01
4
1

Archives
4
extrac-png8
Extract
5
5

Archives
5
joecli-kvpw
joe clip
2
1

Archives
6
joescl-pgfl
Joe's Clip
3
4

Archives
7
bartra-bnm5
Bartra - L
3
1

Archives
8
joescl-fv4u
Joe's clip
5
4

Archives
9
spark-kyzrq
Sparks Bui
1
1

Archives
10
201611-luw8
2016-11-06
2
4

Compares
1
compa-ylxwctg
o 29th Oct
1
2

Compares
2
test-hroqe
test
2
3

Compares
3
compa-ltafnon
o 29th Oct
3
2

Compares
4
compa-tjhacxd
o 27th Oct
1
2

Compares
5
compa-vztxtjc
to 1st Nov
2
4

Compares
6
compa-momncjm
o 19th Nov
1
1

Compares
7
compa-cockhyp
o 19th Dec
4
5

Compares
8
itsac-ihtlze
 a compare
4
1

Compares
9
compa-kycgmav
to 5th Apr
1
2

Compares
10
compa-kdxjbhh
o 14th Dec
2
4

Timelapse
1
longp-mvxla
Long Ponds
1
2

Timelapse
2
basem-hfmjp
Basement Today
2
3

Timelapse
3
3pp-cloxd
3PP
3
4

Timelapse
4
digit-rgblx
Digital HUb
4
1

Timelapse
5
demo-jvnzg
Demo
5
2


WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id AS tab_id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core
WHERE title LIKE '%Pond%';


tab_name
tab_id
exid
title
camera_id
requested_by


Timelapse
1
longp-mvxla
Long Ponds
1
2


WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id AS tab_id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t),
more_info AS
(
  SELECT
    c.tab_name, c.tab_id, c.exid, c.title, c.camera_id, c.requested_by,
    u.lastname, u.email,
    cam.id AS cam_id, cam.name
  FROM core c
  JOIN users u
    ON c.requested_by = u.id
  JOIN cameras cam
    ON c.camera_id = cam.id
)
SELECT * FROM more_info;


tab_name
tab_id
exid
title
camera_id
requested_by
lastname
email
cam_id
name


Archives
1
concre-r6ys
Concrete P
1
1
INTOCCIA
fintocci@its.jnj.com
1
Sisk Mater 1

Archives
2
201809-27xn
2018-09-26
1
1
INTOCCIA
fintocci@its.jnj.com
1
Sisk Mater 1

Archives
3
201902-htu9
2019-02-01
4
1
INTOCCIA
fintocci@its.jnj.com
4
Normal Test Camera

Archives
4
extrac-png8
Extract
5
5
Rahilly
lrahilly@meicltd.com
5
Jubilee Phase 2

Archives
5
joecli-kvpw
joe clip
2
1
INTOCCIA
fintocci@its.jnj.com
2
Stenson Court

Archives
6
joescl-pgfl
Joe's Clip
3
4
Braham
paul.braham@hse.ie
3
DSLR FTP

Archives
7
bartra-bnm5
Bartra - L
3
1
INTOCCIA
fintocci@its.jnj.com
3
DSLR FTP

Archives
8
joescl-fv4u
Joe's clip
5
4
Braham
paul.braham@hse.ie
5
Jubilee Phase 2

Archives
9
spark-kyzrq
Sparks Bui
1
1
INTOCCIA
fintocci@its.jnj.com
1
Sisk Mater 1

Archives
10
201611-luw8
2016-11-06
2
4
Braham
paul.braham@hse.ie
2
Stenson Court

Compares
1
compa-ylxwctg
o 29th Oct
1
2
Kirrane
rkirrane@deniswilliams.ie
1
Sisk Mater 1

Compares
2
test-hroqe
test
2
3
Borowiak
b.borowiak@waterman-moylan.ie
2
Stenson Court

Compares
3
compa-ltafnon
o 29th Oct
3
2
Kirrane
rkirrane@deniswilliams.ie
3
DSLR FTP

Compares
4
compa-tjhacxd
o 27th Oct
1
2
Kirrane
rkirrane@deniswilliams.ie
1
Sisk Mater 1

Compares
5
compa-vztxtjc
to 1st Nov
2
4
Braham
paul.braham@hse.ie
2
Stenson Court

Compares
6
compa-momncjm
o 19th Nov
1
1
INTOCCIA
fintocci@its.jnj.com
1
Sisk Mater 1

Compares
7
compa-cockhyp
o 19th Dec
4
5
Rahilly
lrahilly@meicltd.com
4
Normal Test Camera

Compares
8
itsac-ihtlze
 a compare
4
1
INTOCCIA
fintocci@its.jnj.com
4
Normal Test Camera

Compares
9
compa-kycgmav
to 5th Apr
1
2
Kirrane
rkirrane@deniswilliams.ie
1
Sisk Mater 1

Compares
10
compa-kdxjbhh
o 14th Dec
2
4
Braham
paul.braham@hse.ie
2
Stenson Court

Timelapse
1
longp-mvxla
Long Ponds
1
2
Kirrane
rkirrane@deniswilliams.ie
1
Sisk Mater 1

Timelapse
2
basem-hfmjp
Basement Today
2
3
Borowiak
b.borowiak@waterman-moylan.ie
2
Stenson Court

Timelapse
3
3pp-cloxd
3PP
3
4
Braham
paul.braham@hse.ie
3
DSLR FTP

Timelapse
4
digit-rgblx
Digital HUb
4
1
INTOCCIA
fintocci@its.jnj.com
4
Normal Test Camera

Timelapse
5
demo-jvnzg
Demo
5
2
Kirrane
rkirrane@deniswilliams.ie
5
Jubilee Phase 2


SELECT 
  ar.title, ar.exid, ar.status, ar.created_at, u.email requester_email, c.name as camera_name 
FROM archives ar 
LEFT JOIN users u ON ar.requested_by = u.id 
LEFT JOIN cameras c ON ar.camera_id = c.id

UNION

SELECT 
  tl.title, tl.exid, tl.status, tl.inserted_at, u.email, c.name as camera_name 
FROM timelapses tl 
LEFT JOIN users u ON tl.user_id = u.id 
LEFT JOIN cameras c ON tl.camera_id = c.id

UNION

SELECT cm.name, cm.exid, cm.status, cm.inserted_at, u.email, c.name as camera_name 
FROM compares cm 
LEFT JOIN users u ON cm.requested_by = u.id 
LEFT JOIN cameras c ON cm.camera_id = c.id


title
exid
status
created_at
requester_email
camera_name


2016-11-06 BWLiffeyCruises 1200-1230
201611-luw8
2
2016-11-23 09:36:32+00
paul.braham@hse.ie
Stenson Court

Joe's clip
joescl-fv4u
2
2017-04-21 10:25:13+01
paul.braham@hse.ie
Jubilee Phase 2

Long Ponds
longp-mvxla
3
2017-05-18 08:16:13+01
rkirrane@deniswilliams.ie
Sisk Mater 1

3PP
3pp-cloxd
3
2017-05-18 08:16:12+01
paul.braham@hse.ie
DSLR FTP

Its a compare
itsac-ihtlze
1
2020-01-01 08:46:23.172885+00
fintocci@its.jnj.com
Normal Test Camera

test
test-hroqe
1
2018-01-17 11:08:19.845168+00
b.borowiak@waterman-moylan.ie
Stenson Court

2019-02-01T10:13:47.000Z
201902-htu9
2
2019-02-01 10:14:56+00
fintocci@its.jnj.com
Normal Test Camera

Digital HUb
digit-rgblx
3
2017-05-18 08:16:10+01
fintocci@its.jnj.com
Normal Test Camera

Basement Today
basem-hfmjp
6
2019-01-22 11:49:46.333172+00
b.borowiak@waterman-moylan.ie
Stenson Court

Joe's Clip
joescl-pgfl
2
2017-04-21 10:23:47+01
paul.braham@hse.ie
DSLR FTP

Bartra - Loughshinny Time-lapse, October 2018
bartra-bnm5
2
2018-10-15 14:49:51.783621+01
fintocci@its.jnj.com
DSLR FTP

Compare: 4th Dec to 14th Dec
compa-kdxjbhh
1
2018-12-14 22:25:00.735079+00
paul.braham@hse.ie
Stenson Court

Extract
extrac-png8
2
2017-04-18 09:50:42+01
lrahilly@meicltd.com
Jubilee Phase 2

joe clip
joecli-kvpw
2
2017-04-20 00:02:35+01
fintocci@its.jnj.com
Stenson Court

Compare: 11th Jan to 19th Dec
compa-cockhyp
1
2018-12-19 14:13:35.856163+00
lrahilly@meicltd.com
Normal Test Camera

Demo
demo-jvnzg
3
2017-05-18 08:16:10+01
rkirrane@deniswilliams.ie
Jubilee Phase 2

Compare: 18th Jul to 1st Nov
compa-vztxtjc
1
2018-11-15 10:03:24.85408+00
paul.braham@hse.ie
Stenson Court

2018-09-26T10:00:04.000Z
201809-27xn
2
2018-11-27 16:05:01.813853+00
fintocci@its.jnj.com
Sisk Mater 1

Concrete Pour
concre-r6ys
2
2017-04-05 15:46:38+01
fintocci@its.jnj.com
Sisk Mater 1

Compare: 1st Nov to 5th Apr
compa-kycgmav
1
2019-04-08 14:54:03.809314+01
rkirrane@deniswilliams.ie
Sisk Mater 1

Compare: 11th Oct to 19th Nov
compa-momncjm
1
2018-11-19 15:28:52.359459+00
fintocci@its.jnj.com
Sisk Mater 1

Compare: 26th Jul to 29th Oct
compa-ltafnon
1
2018-10-29 05:53:39.989565+00
rkirrane@deniswilliams.ie
DSLR FTP

Compare: 23rd Jan to 27th Oct
compa-tjhacxd
1
2018-10-29 05:56:52.67979+00
rkirrane@deniswilliams.ie
Sisk Mater 1

Sparks Building, Newcastle - Time-lapse, July 2020
spark-kyzrq
2
2020-07-28 16:25:33.449623+01
fintocci@its.jnj.com
Sisk Mater 1

Compare: 8th Sep to 29th Oct
compa-ylxwctg
1
2018-10-29 05:52:09.659566+00
rkirrane@deniswilliams.ie
Sisk Mater 1


--
-- Performance
--
EXPLAIN (ANALYZE, BUFFERS)
WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;


QUERY PLAN


Append  (cost=0.00..33.48 rows=130 width=108) (actual time=0.013..0.045 rows=25 loops=1)

  Buffers: shared hit=3

  ->  Seq Scan on archives a  (cost=0.00..10.50 rows=40 width=108) (actual time=0.012..0.016 rows=10 loops=1)

        Buffers: shared hit=1

  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..11.12 rows=50 width=108) (actual time=0.008..0.014 rows=10 loops=1)

        Buffers: shared hit=1

        ->  Seq Scan on compares c  (cost=0.00..10.62 rows=50 width=592) (actual time=0.007..0.012 rows=10 loops=1)

              Buffers: shared hit=1

  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..10.80 rows=40 width=108) (actual time=0.007..0.009 rows=5 loops=1)

        Buffers: shared hit=1

        ->  Seq Scan on timelapses t  (cost=0.00..10.40 rows=40 width=1076) (actual time=0.006..0.008 rows=5 loops=1)

              Buffers: shared hit=1

Planning Time: 0.255 ms

Execution Time: 0.086 ms


EXPLAIN (ANALYZE, BUFFERS)
SELECT 
  ar.title, ar.exid, ar.status, ar.created_at, u.email requester_email, c.name as camera_name 
FROM archives ar 
LEFT JOIN users u ON ar.requested_by = u.id 
LEFT JOIN cameras c ON ar.camera_id = c.id

UNION

SELECT 
  tl.title, tl.exid, tl.status, tl.inserted_at, u.email, c.name as camera_name 
FROM timelapses tl 
LEFT JOIN users u ON tl.user_id = u.id 
LEFT JOIN cameras c ON tl.camera_id = c.id

UNION

SELECT cm.name, cm.exid, cm.status, cm.inserted_at, u.email, c.name as camera_name 
FROM compares cm 
LEFT JOIN users u ON cm.requested_by = u.id 
LEFT JOIN cameras c ON cm.camera_id = c.id;


QUERY PLAN


HashAggregate  (cost=105.81..107.11 rows=130 width=140) (actual time=0.554..0.565 rows=25 loops=1)

  Group Key: ar.title, ar.exid, ar.status, ar.created_at, u.email, c.name

  Buffers: shared hit=9

  ->  Append  (cost=22.02..103.86 rows=130 width=140) (actual time=0.084..0.534 rows=25 loops=1)

        Buffers: shared hit=9

        ->  Hash Left Join  (cost=22.02..33.78 rows=40 width=140) (actual time=0.083..0.223 rows=10 loops=1)

              Hash Cond: (ar.requested_by = u.id)

              Buffers: shared hit=3

              ->  Hash Right Join  (cost=10.90..22.54 rows=40 width=112) (actual time=0.048..0.183 rows=10 loops=1)

                    Hash Cond: (c.id = ar.camera_id)

                    Buffers: shared hit=2

                    ->  Seq Scan on cameras c  (cost=0.00..10.90 rows=90 width=36) (actual time=0.006..0.009 rows=10 loops=1)

                          Buffers: shared hit=1

                    ->  Hash  (cost=10.40..10.40 rows=40 width=84) (actual time=0.025..0.025 rows=10 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          Buffers: shared hit=1

                          ->  Seq Scan on archives ar  (cost=0.00..10.40 rows=40 width=84) (actual time=0.011..0.014 rows=10 loops=1)

                                Buffers: shared hit=1

              ->  Hash  (cost=10.50..10.50 rows=50 width=36) (actual time=0.019..0.019 rows=10 loops=1)

                    Buckets: 1024  Batches: 1  Memory Usage: 9kB

                    Buffers: shared hit=1

                    ->  Seq Scan on users u  (cost=0.00..10.50 rows=50 width=36) (actual time=0.007..0.010 rows=10 loops=1)

                          Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 2"  (cost=22.02..34.28 rows=40 width=140) (actual time=0.191..0.203 rows=5 loops=1)

              Buffers: shared hit=3

              ->  Hash Left Join  (cost=22.02..33.78 rows=40 width=1108) (actual time=0.189..0.198 rows=5 loops=1)

                    Hash Cond: (tl.user_id = u_1.id)

                    Buffers: shared hit=3

                    ->  Hash Right Join  (cost=10.90..22.54 rows=40 width=1080) (actual time=0.150..0.157 rows=5 loops=1)

                          Hash Cond: (c_1.id = tl.camera_id)

                          Buffers: shared hit=2

                          ->  Seq Scan on cameras c_1  (cost=0.00..10.90 rows=90 width=36) (actual time=0.009..0.010 rows=10 loops=1)

                                Buffers: shared hit=1

                          ->  Hash  (cost=10.40..10.40 rows=40 width=1052) (actual time=0.115..0.115 rows=5 loops=1)

                                Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                Buffers: shared hit=1

                                ->  Seq Scan on timelapses tl  (cost=0.00..10.40 rows=40 width=1052) (actual time=0.010..0.015 rows=5 loops=1)

                                      Buffers: shared hit=1

                    ->  Hash  (cost=10.50..10.50 rows=50 width=36) (actual time=0.018..0.019 rows=10 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          Buffers: shared hit=1

                          ->  Seq Scan on users u_1  (cost=0.00..10.50 rows=50 width=36) (actual time=0.007..0.010 rows=10 loops=1)

                                Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 3"  (cost=22.25..34.75 rows=50 width=140) (actual time=0.088..0.103 rows=10 loops=1)

              Buffers: shared hit=3

              ->  Hash Left Join  (cost=22.25..34.13 rows=50 width=1108) (actual time=0.086..0.098 rows=10 loops=1)

                    Hash Cond: (cm.requested_by = u_2.id)

                    Buffers: shared hit=3

                    ->  Hash Right Join  (cost=11.12..22.86 rows=50 width=1080) (actual time=0.051..0.060 rows=10 loops=1)

                          Hash Cond: (c_2.id = cm.camera_id)

                          Buffers: shared hit=2

                          ->  Seq Scan on cameras c_2  (cost=0.00..10.90 rows=90 width=36) (actual time=0.006..0.007 rows=10 loops=1)

                                Buffers: shared hit=1

                          ->  Hash  (cost=10.50..10.50 rows=50 width=1052) (actual time=0.020..0.021 rows=10 loops=1)

                                Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                Buffers: shared hit=1

                                ->  Seq Scan on compares cm  (cost=0.00..10.50 rows=50 width=1052) (actual time=0.008..0.011 rows=10 loops=1)

                                      Buffers: shared hit=1

                    ->  Hash  (cost=10.50..10.50 rows=50 width=36) (actual time=0.018..0.018 rows=10 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          Buffers: shared hit=1

                          ->  Seq Scan on users u_2  (cost=0.00..10.50 rows=50 width=36) (actual time=0.007..0.009 rows=10 loops=1)

                                Buffers: shared hit=1

Planning Time: 0.800 ms

Execution Time: 0.643 ms


EXPLAIN (ANALYZE, BUFFERS)
WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id AS tab_id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t),
more_info AS
(
  SELECT
    c.tab_name, c.tab_id, c.exid, c.title, c.camera_id, c.requested_by,
    u.lastname, u.email,
    cam.id AS cam_id, cam.name
  FROM core c
  JOIN users u
    ON c.requested_by = u.id
  JOIN cameras cam
    ON c.camera_id = cam.id
)
SELECT * FROM more_info;


QUERY PLAN


Hash Join  (cost=23.15..58.41 rows=35 width=208) (actual time=0.064..0.115 rows=25 loops=1)

  Hash Cond: (a.camera_id = cam.id)

  Buffers: shared hit=5

  ->  Hash Join  (cost=11.12..46.25 rows=50 width=172) (actual time=0.033..0.075 rows=25 loops=1)

        Hash Cond: (a.requested_by = u.id)

        Buffers: shared hit=4

        ->  Append  (cost=0.00..33.48 rows=130 width=108) (actual time=0.008..0.042 rows=25 loops=1)

              Buffers: shared hit=3

              ->  Seq Scan on archives a  (cost=0.00..10.50 rows=40 width=108) (actual time=0.008..0.013 rows=10 loops=1)

                    Buffers: shared hit=1

              ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..11.12 rows=50 width=108) (actual time=0.008..0.015 rows=10 loops=1)

                    Buffers: shared hit=1

                    ->  Seq Scan on compares c  (cost=0.00..10.62 rows=50 width=592) (actual time=0.008..0.012 rows=10 loops=1)

                          Buffers: shared hit=1

              ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..10.80 rows=40 width=108) (actual time=0.007..0.009 rows=5 loops=1)

                    Buffers: shared hit=1

                    ->  Seq Scan on timelapses t  (cost=0.00..10.40 rows=40 width=1076) (actual time=0.006..0.008 rows=5 loops=1)

                          Buffers: shared hit=1

        ->  Hash  (cost=10.50..10.50 rows=50 width=68) (actual time=0.019..0.019 rows=10 loops=1)

              Buckets: 1024  Batches: 1  Memory Usage: 9kB

              Buffers: shared hit=1

              ->  Seq Scan on users u  (cost=0.00..10.50 rows=50 width=68) (actual time=0.013..0.015 rows=10 loops=1)

                    Buffers: shared hit=1

  ->  Hash  (cost=10.90..10.90 rows=90 width=36) (actual time=0.018..0.018 rows=10 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        Buffers: shared hit=1

        ->  Seq Scan on cameras cam  (cost=0.00..10.90 rows=90 width=36) (actual time=0.011..0.013 rows=10 loops=1)

              Buffers: shared hit=1

Planning Time: 0.598 ms

Execution Time: 0.205 ms


EXPLAIN(ANALYZE, BUFFERS)
WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id AS tab_id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core
WHERE title LIKE '%Pond%';


QUERY PLAN


Subquery Scan on core  (cost=0.00..35.10 rows=1 width=108) (actual time=0.065..0.070 rows=1 loops=1)

  Filter: (core.title ~~ '%Pond%'::text)

  Rows Removed by Filter: 24

  Buffers: shared hit=3

  ->  Append  (cost=0.00..33.48 rows=130 width=108) (actual time=0.012..0.063 rows=25 loops=1)

        Buffers: shared hit=3

        ->  Seq Scan on archives a  (cost=0.00..10.50 rows=40 width=108) (actual time=0.012..0.016 rows=10 loops=1)

              Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..11.12 rows=50 width=108) (actual time=0.008..0.014 rows=10 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on compares c  (cost=0.00..10.62 rows=50 width=592) (actual time=0.007..0.011 rows=10 loops=1)

                    Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..10.80 rows=40 width=108) (actual time=0.026..0.029 rows=5 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on timelapses t  (cost=0.00..10.40 rows=40 width=1076) (actual time=0.026..0.027 rows=5 loops=1)

                    Buffers: shared hit=1

Planning Time: 0.170 ms

Execution Time: 0.094 ms
tab_name	id	exid	title	camera_id	requested_by
Archives	1	concre-r6ys	Concrete P	1	1
Archives	2	201809-27xn	2018-09-26	1	1
Archives	3	201902-htu9	2019-02-01	4	1
Archives	4	extrac-png8	Extract	5	5
Archives	5	joecli-kvpw	joe clip	2	1
Archives	6	joescl-pgfl	Joe's Clip	3	4
Archives	7	bartra-bnm5	Bartra - L	3	1
Archives	8	joescl-fv4u	Joe's clip	5	4
Archives	9	spark-kyzrq	Sparks Bui	1	1
Archives	10	201611-luw8	2016-11-06	2	4
Compares	1	compa-ylxwctg	o 29th Oct	1	2
Compares	2	test-hroqe	test	2	3
Compares	3	compa-ltafnon	o 29th Oct	3	2
Compares	4	compa-tjhacxd	o 27th Oct	1	2
Compares	5	compa-vztxtjc	to 1st Nov	2	4
Compares	6	compa-momncjm	o 19th Nov	1	1
Compares	7	compa-cockhyp	o 19th Dec	4	5
Compares	8	itsac-ihtlze	a compare	4	1
Compares	9	compa-kycgmav	to 5th Apr	1	2
Compares	10	compa-kdxjbhh	o 14th Dec	2	4
Timelapse	1	longp-mvxla	Long Ponds	1	2
Timelapse	2	basem-hfmjp	Basement Today	2	3
Timelapse	3	3pp-cloxd	3PP	3	4
Timelapse	4	digit-rgblx	Digital HUb	4	1
Timelapse	5	demo-jvnzg	Demo	5	2
title	exid	status	created_at	requester_email	camera_name
2016-11-06 BWLiffeyCruises 1200-1230	201611-luw8	2	2016-11-23 09:36:32+00	paul.braham@hse.ie	Stenson Court
Joe's clip	joescl-fv4u	2	2017-04-21 10:25:13+01	paul.braham@hse.ie	Jubilee Phase 2
Long Ponds	longp-mvxla	3	2017-05-18 08:16:13+01	rkirrane@deniswilliams.ie	Sisk Mater 1
3PP	3pp-cloxd	3	2017-05-18 08:16:12+01	paul.braham@hse.ie	DSLR FTP
Its a compare	itsac-ihtlze	1	2020-01-01 08:46:23.172885+00	fintocci@its.jnj.com	Normal Test Camera
test	test-hroqe	1	2018-01-17 11:08:19.845168+00	b.borowiak@waterman-moylan.ie	Stenson Court
2019-02-01T10:13:47.000Z	201902-htu9	2	2019-02-01 10:14:56+00	fintocci@its.jnj.com	Normal Test Camera
Digital HUb	digit-rgblx	3	2017-05-18 08:16:10+01	fintocci@its.jnj.com	Normal Test Camera
Basement Today	basem-hfmjp	6	2019-01-22 11:49:46.333172+00	b.borowiak@waterman-moylan.ie	Stenson Court
Joe's Clip	joescl-pgfl	2	2017-04-21 10:23:47+01	paul.braham@hse.ie	DSLR FTP
Bartra - Loughshinny Time-lapse, October 2018	bartra-bnm5	2	2018-10-15 14:49:51.783621+01	fintocci@its.jnj.com	DSLR FTP
Compare: 4th Dec to 14th Dec	compa-kdxjbhh	1	2018-12-14 22:25:00.735079+00	paul.braham@hse.ie	Stenson Court
Extract	extrac-png8	2	2017-04-18 09:50:42+01	lrahilly@meicltd.com	Jubilee Phase 2
joe clip	joecli-kvpw	2	2017-04-20 00:02:35+01	fintocci@its.jnj.com	Stenson Court
Compare: 11th Jan to 19th Dec	compa-cockhyp	1	2018-12-19 14:13:35.856163+00	lrahilly@meicltd.com	Normal Test Camera
Demo	demo-jvnzg	3	2017-05-18 08:16:10+01	rkirrane@deniswilliams.ie	Jubilee Phase 2
Compare: 18th Jul to 1st Nov	compa-vztxtjc	1	2018-11-15 10:03:24.85408+00	paul.braham@hse.ie	Stenson Court
2018-09-26T10:00:04.000Z	201809-27xn	2	2018-11-27 16:05:01.813853+00	fintocci@its.jnj.com	Sisk Mater 1
Concrete Pour	concre-r6ys	2	2017-04-05 15:46:38+01	fintocci@its.jnj.com	Sisk Mater 1
Compare: 1st Nov to 5th Apr	compa-kycgmav	1	2019-04-08 14:54:03.809314+01	rkirrane@deniswilliams.ie	Sisk Mater 1
Compare: 11th Oct to 19th Nov	compa-momncjm	1	2018-11-19 15:28:52.359459+00	fintocci@its.jnj.com	Sisk Mater 1
Compare: 26th Jul to 29th Oct	compa-ltafnon	1	2018-10-29 05:53:39.989565+00	rkirrane@deniswilliams.ie	DSLR FTP
Compare: 23rd Jan to 27th Oct	compa-tjhacxd	1	2018-10-29 05:56:52.67979+00	rkirrane@deniswilliams.ie	Sisk Mater 1
Sparks Building, Newcastle - Time-lapse, July 2020	spark-kyzrq	2	2020-07-28 16:25:33.449623+01	fintocci@its.jnj.com	Sisk Mater 1
Compare: 8th Sep to 29th Oct	compa-ylxwctg	1	2018-10-29 05:52:09.659566+00	rkirrane@deniswilliams.ie	Sisk Mater 1