Postgres 13

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 audit_trail AS
SELECT * FROM (
  VALUES
     ('harold_gim@yahoo.com', 'hgimenez@hotmail.com')
   , ('hgimenez@hotmail.com', 'harold.gimenez@gmail.com')
   , ('harold.gimenez@gmail.com', 'harold@heroku.com')
   , ('foo@bar.com', 'bar@baz.com')
   , ('bar@baz.com', 'barbaz@gmail.com')
   ) t(old_email, new_email);

CREATE TABLE iter1 AS
SELECT old_email, new_email 
FROM   audit_trail
WHERE  old_email = 'harold_gim@yahoo.com';

CREATE TABLE iter2 AS
SELECT a.old_email, a.new_email
FROM   audit_trail a
JOIN   iter1 b ON (a.old_email = b.new_email);

SELECT 5



SELECT 1



SELECT 1


TABLE audit_trail;


old_email
new_email


harold_gim@yahoo.com
hgimenez@hotmail.com

hgimenez@hotmail.com
harold.gimenez@gmail.com

harold.gimenez@gmail.com
harold@heroku.com

foo@bar.com
bar@baz.com

bar@baz.com
barbaz@gmail.com



SELECT 5


TABLE iter1;


old_email
new_email


harold_gim@yahoo.com
hgimenez@hotmail.com



SELECT 1


TABLE iter2;


old_email
new_email


hgimenez@hotmail.com
harold.gimenez@gmail.com



SELECT 1


WITH RECURSIVE all_emails AS (
   SELECT  *
   FROM    audit_trail
   WHERE   old_email = 'harold_gim@yahoo.com'

   UNION ALL  -- union all!
   SELECT t.*
   FROM   all_emails  a
   JOIN   audit_trail t ON t.old_email = a.new_email
)
TABLE  all_emails;


old_email
new_email


harold_gim@yahoo.com
hgimenez@hotmail.com

hgimenez@hotmail.com
harold.gimenez@gmail.com

harold.gimenez@gmail.com
harold@heroku.com



SELECT 3


SELECT * FROM iter1
UNION
SELECT * FROM iter2;


old_email
new_email


harold_gim@yahoo.com
hgimenez@hotmail.com

hgimenez@hotmail.com
harold.gimenez@gmail.com



SELECT 2


SELECT * FROM iter1
UNION ALL  -- union all!
SELECT * FROM iter2;


old_email
new_email


harold_gim@yahoo.com
hgimenez@hotmail.com

hgimenez@hotmail.com
harold.gimenez@gmail.com



SELECT 2


-- to be sure:
WITH RECURSIVE all_emails AS (
   SELECT  *, 1 AS lvl
   FROM    audit_trail
   WHERE   old_email = 'harold_gim@yahoo.com'

   UNION ALL  -- union all!
   SELECT t.*, a.lvl + 1
   FROM   all_emails  a
   JOIN   audit_trail t ON t.old_email = a.new_email
)
TABLE  all_emails
ORDER  BY lvl;


old_email
new_email
lvl


harold_gim@yahoo.com
hgimenez@hotmail.com
1

hgimenez@hotmail.com
harold.gimenez@gmail.com
2

harold.gimenez@gmail.com
harold@heroku.com
3



SELECT 3


-- for multiple trails at once:
WITH RECURSIVE all_emails AS (
   SELECT  *, ctid AS trail_id, 1 AS lvl
   FROM    audit_trail
   WHERE   old_email IN ('harold_gim@yahoo.com', 'foo@bar.com')

   UNION ALL  -- union all!
   SELECT t.*, a.trail_id, a.lvl + 1
   FROM   all_emails  a
   JOIN   audit_trail t ON t.old_email = a.new_email
)
TABLE  all_emails
ORDER  BY trail_id, lvl;


old_email
new_email
trail_id
lvl


harold_gim@yahoo.com
hgimenez@hotmail.com
(0,1)
1

hgimenez@hotmail.com
harold.gimenez@gmail.com
(0,1)
2

harold.gimenez@gmail.com
harold@heroku.com
(0,1)
3

foo@bar.com
bar@baz.com
(0,4)
1

bar@baz.com
barbaz@gmail.com
(0,4)
2



SELECT 5

old_email	new_email
harold_gim@yahoo.com	hgimenez@hotmail.com
hgimenez@hotmail.com	harold.gimenez@gmail.com
harold.gimenez@gmail.com	harold@heroku.com
foo@bar.com	bar@baz.com
bar@baz.com	barbaz@gmail.com

old_email	new_email	trail_id	lvl
harold_gim@yahoo.com	hgimenez@hotmail.com	(0,1)	1
hgimenez@hotmail.com	harold.gimenez@gmail.com	(0,1)	2
harold.gimenez@gmail.com	harold@heroku.com	(0,1)	3
foo@bar.com	bar@baz.com	(0,4)	1
bar@baz.com	barbaz@gmail.com	(0,4)	2