View difference between Paste ID: 697EfFMQ and nzwCGxD2

--- Table: emails
+--DROP TABLE emails;
 
-DROP TABLE emails;
+
 (
   id serial NOT NULL,
   subject text,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   folder text,
   message_id text,
   CONSTRAINT emails_pkey PRIMARY KEY (id)
-  "references" text[],
+
 WITH (
   OIDS=FALSE
 );
 
 -- Index: emails_message_id_index
 
 -- DROP INDEX emails_message_id_index;
 
 CREATE INDEX emails_message_id_index
   ON emails
   USING btree
   (message_id COLLATE pg_catalog."default");
 
  INSERT INTO emails(
     subject, created_at, updated_at, folder, message_id)
     VALUES ('howzitgoin', now(), now(), null, '53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');
 
-    subject, created_at, updated_at, folder,  "references", message_id)
+
-    VALUES ('root', now(), now(), 'INBOX', '{}', 'CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com');
+    subject, created_at, updated_at, folder, message_id)
     VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com');
 
-    subject, created_at, updated_at, folder,  "references", message_id)
+
-    VALUES ('one', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com}','536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail');
+    subject, created_at, updated_at, folder, message_id)
     VALUES ('Re: howzitgoin', now(), now(), 'INBOX', '53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');
 
-    subject, created_at, updated_at, folder,  "references", message_id)
+
-    VALUES ('two', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail}','CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com');
+    subject, created_at, updated_at, folder, message_id)
     VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YT6vx6buOKUga4f=bcNGq_=WzwiqEzm2FWm3HoLZ8SbJA@mail.gmail.com');
 
-    subject, created_at, updated_at, folder,  "references", message_id)
+
-    VALUES ('three', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail,CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com}','536b9d5b6d64c_23fd346dd78f0111bf@9369fd83-7877-4eec-8cc8-4b207abbae85.mail');
+    subject, created_at, updated_at, folder,  message_id)
     VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');
 
-    subject, created_at, updated_at, folder,  "references", message_id)
+
-    VALUES ('four', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail,CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com,536b9d5b6d64c_23fd346dd78f0111bf@9369fd83-7877-4eec-8cc8-4b207abbae85.mail}','CAEBV8YR97FSa5Bf-_+NrkV+edDM0R-NwSQmW0CXGwyg0EuG6qg@mail.gmail.com');
+    subject, created_at, updated_at, folder,  message_id)
     VALUES ('Not part of thread', now(), now(), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');
-SELECT DISTINCT e.id, folder, subject, message_id, "references", e.updated_at,
+
-(select count(message_id)  from emails  where (select "references"[1] from emails where message_id = e.message_id) = ANY ("references") or message_id = (select "references"[1] from emails where message_id = e.message_id)) as replies
+-- Table: replies
-FROM "emails" e
+
-WHERE ("folder" = 'INBOX');
+-- DROP TABLE replies;
 
 CREATE TABLE replies
 (
   id serial NOT NULL,
   message_id text,
   created_at timestamp without time zone,
   email_id integer NOT NULL,
   CONSTRAINT replies_pkey PRIMARY KEY (id),
   CONSTRAINT replies_email_id_fkey FOREIGN KEY (email_id)
       REFERENCES emails (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE CASCADE
 )
 WITH (
   OIDS=FALSE
 );
 
 
 -- Index: replies_email_id_index
 
 -- DROP INDEX replies_email_id_index;
 
 CREATE INDEX replies_email_id_index
   ON replies
   USING btree
   (email_id);
 
 -- Index: replies_message_id_index
 
 -- DROP INDEX replies_message_id_index;
 
 CREATE INDEX replies_message_id_index
   ON replies
   USING btree
   (message_id COLLATE pg_catalog."default");
 
 
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 2);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 3);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 3);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 5);
 
 INSERT INTO replies(message_id, created_at, email_id)
             VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);
 
 
 SELECT  "emails".id, "emails".message_id, subject, "count", "emails".created_at, "count"
 FROM "emails"
 LEFT JOIN
 (
   SELECT "email_id", count(*) AS "count" FROM "replies" GROUP BY "email_id"
 ) AS "r"
 ON ("emails"."id" = "r"."email_id")
 WHERE (("folder" = 'INBOX')
   --AND ("emails"."id" NOT IN (SELECT "email_id" FROM "replies"))
 ) ORDER BY "created_at" DESC LIMIT 7 OFFSET 0