distinct-vs-limit-1-union-all.md

state

SELECT
  queue,
  count(1) FILTER (WHERE state = 'available'),
  count(1) FILTER (WHERE state IS NULL OR state != 'available')
FROM oban_jobs
GROUP BY queue;

a	100000	100000
b	100000	100000
c	100000	100000
d	100000	100000
e	100000	100000
f	100000	100000

(so 2*6*100k = 1.2M jobs)

SELECT DISTINCT queue FROM oban_producers;

a
b
c
d
e
f
g
h
i
j
k

equivalent queries

distinct

100ms:

SELECT DISTINCT queue
FROM oban_jobs
WHERE state = 'available' AND NOT queue IS NULL;

limit 1 union all

3ms:

(SELECT 'a' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'a' LIMIT 1)
UNION ALL
(SELECT 'b' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'b' LIMIT 1)
UNION ALL
(SELECT 'c' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'c' LIMIT 1)
UNION ALL
(SELECT 'd' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'd' LIMIT 1)
UNION ALL
(SELECT 'e' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'e' LIMIT 1)
UNION ALL
(SELECT 'f' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'f' LIMIT 1)
UNION ALL
(SELECT 'g' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'g' LIMIT 1)
UNION ALL
(SELECT 'h' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'h' LIMIT 1)
UNION ALL
(SELECT 'i' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'i' LIMIT 1)
UNION ALL
(SELECT 'j' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'j' LIMIT 1)
UNION ALL
(SELECT 'k' FROM oban_jobs WHERE state = 'available' AND queue IS NOT NULL AND queue = 'k' LIMIT 1);

auto generated limit 1 union all

CREATE OR REPLACE FUNCTION queues_with_jobs() RETURNS TABLE (queue TEXT) AS
$BODY$
DECLARE
  queues varchar[];
  queue varchar;
  query varchar := '';
  i smallint := 1;
  l smallint;
BEGIN
  SELECT array_agg(DISTINCT(p.queue::varchar)) FROM oban_producers p INTO queues;
  l := array_length(queues, 1);

  FOREACH queue IN ARRAY queues LOOP
    query := query || format('(SELECT %L FROM oban_jobs WHERE state = ''available'' AND queue = %L LIMIT 1)', queue, queue);
    IF i < l THEN
      query := query || ' UNION ALL ';
    END IF;
    i := i+1;
  END LOOP;

  --RAISE WARNING 'Generated query: %', query;
  RETURN QUERY EXECUTE query;
END
$BODY$
LANGUAGE plpgsql;

3ms:

SELECT queues_with_jobs();