Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE b (
  org_id   int  PRIMARY KEY
, org_name text UNIQUE
);

CREATE TABLE a (
  id         serial PRIMARY KEY
, uuid       uuid
, name       text
, type       int
, created_by int
, org_id     int REFERENCES b
);

-- minimal demo data
INSERT INTO b VALUES
  (1, 'A_myorg')
, (2, 'B_myorg')
, (3, 'C_mXorg')  -- no match
;

INSERT INTO a(org_id, created_by) VALUES
  (1, 1)
, (1, 1)
, (1, 2)
-- many more ??
, (2, 1)
, (2, 3)
, (2, 3)
-- many more ??
, (3, 1)
, (3, 4)
, (3, 5)
;

CREATE INDEX foo_idx ON a (org_id, created_by);

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 9



CREATE INDEX


SELECT count(DISTINCT created_by)  -- does not count NULL (as desired)
FROM   b
CROSS  JOIN LATERAL (
   WITH RECURSIVE t AS (
      (  -- parentheses required
      SELECT created_by
      FROM   a
      WHERE  org_id = b.org_id
      ORDER  BY created_by
      LIMIT 1
      )
      UNION ALL
      SELECT (SELECT created_by
              FROM   a
              WHERE  org_id = b.org_id
              AND    created_by > t.created_by
              ORDER  BY created_by
              LIMIT  1)
      FROM   t
      WHERE  t.created_by IS NOT NULL  -- stop recursion
      )
   TABLE t
   ) a
WHERE  b.org_name LIKE '%myorg%';


count


3



SELECT 1


SET enable_seqscan = false;  -- to force the index plan for minimal table

EXPLAIN
SELECT count(DISTINCT created_by)  -- does not count NULL (as desired)
FROM   b
CROSS  JOIN LATERAL (
   WITH RECURSIVE t AS (
      (  -- parentheses required
      SELECT created_by
      FROM   a
      WHERE  org_id = b.org_id
      ORDER  BY created_by
      LIMIT 1
      )
      UNION ALL
      SELECT (SELECT created_by
              FROM   a
              WHERE  org_id = b.org_id
              AND    created_by > t.created_by
              ORDER  BY created_by
              LIMIT  1)
      FROM   t
      WHERE  t.created_by IS NOT NULL  -- stop recursion
      )
   TABLE t
   ) a
WHERE  b.org_name LIKE '%myorg%';

SET




QUERY PLAN


Aggregate  (cost=10000001689.81..10000001689.82 rows=1 width=8)

  ->  Nested Loop  (cost=10000000827.67..10000001689.30 rows=202 width=4)

        ->  Seq Scan on b  (cost=10000000000.00..10000000025.88 rows=2 width=4)

              Filter: (org_name ~~ '%myorg%'::text)

        ->  CTE Scan on t  (cost=827.67..829.69 rows=101 width=4)

              CTE t

                ->  Recursive Union  (cost=0.14..827.67 rows=101 width=4)

                      ->  Limit  (cost=0.14..8.15 rows=1 width=4)

                            ->  Index Only Scan using foo_idx on a  (cost=0.14..8.15 rows=1 width=4)

                                  Index Cond: (org_id = $1)

                      ->  WorkTable Scan on t t_1  (cost=0.00..81.75 rows=10 width=4)

                            Filter: (created_by IS NOT NULL)



EXPLAIN