Postgres 10

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 tag (
  tag_id serial
, tag    text NOT NULL
);

CREATE TABLE task (
  task_id  serial
, task     text NOT NULL
);

CREATE TABLE task_tag (
  task_id    int
, tag_id     int
);

INSERT INTO tag  (tag)  SELECT 'tag'  || g FROM generate_series(1,5000) g;
INSERT INTO task (task) SELECT 'task' || g FROM generate_series(1,5000) g;
INSERT INTO task_tag (task_id, tag_id)
SELECT task_id, tag_id
FROM   task ts, LATERAL (SELECT tag_id FROM tag WHERE random() > 0.996) tg;

ALTER TABLE task     ADD PRIMARY KEY (task_id);
ALTER TABLE tag      ADD PRIMARY KEY (tag_id);
ALTER TABLE task_tag ADD PRIMARY KEY (tag_id, task_id)  -- columns in this order
 , ADD CONSTRAINT task_task_id_fkey FOREIGN KEY (task_id) REFERENCES task (task_id)
 , ADD CONSTRAINT tag_tag_id_fkey   FOREIGN KEY (tag_id)  REFERENCES tag  (tag_id);

INSERT INTO task_tag (task_id, tag_id) VALUES
   (123, 123), (123, 456)
 , (321, 123), (321, 456)
ON CONFLICT DO NOTHING;  -- make sure we have two hits

CREATE INDEX tag_covering_idx ON tag (tag_id, tag);

ANALYZE tag;
ANALYZE task;
ANALYZE task_tag;
5000 rows affected

5000 rows affected

120000 rows affected

4 rows affected

CREATE OR REPLACE FUNCTION f_tasks_with_tags(VARIADIC _tags text[])
  RETURNS SETOF task AS
$func$
   WITH RECURSIVE cte AS (
      SELECT task_id, 1 AS idx
      FROM   task_tag
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[1])

      UNION
      SELECT task_id, c.idx + 1
      FROM   cte c
      JOIN   task_tag tt USING (task_id)
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[c.idx + 1])
      )
   SELECT t.*
   FROM   cte  c
   JOIN   task t USING (task_id)
   WHERE  c.idx = array_length(_tags, 1)
$func$  LANGUAGE sql STABLE;
SELECT * FROM f_tasks_with_tags('tag123', 'tag456');


task_id
task


321
task321

123
task123