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 posts_lists AS
  SELECT id,
         ARRAY[abs(trunc(random() * 100 - 100))::INTEGER,trunc(random() * 100 + 100)::INTEGER] AS tag_ids,
         trunc(random() * 1000) :: INTEGER                                                     AS score
  FROM generate_series(1, 100000) AS id;
ALTER TABLE posts_lists
  ADD PRIMARY KEY (id);
CREATE INDEX posts_lists_idx_score_id
  ON posts_lists (score DESC, id DESC);
CREATE INDEX posts_lists_idx_tag_ids
  ON posts_lists
  USING gin (tag_ids);
100000 rows affected

CREATE TABLE tags AS
  SELECT tag_id AS id, tag_id :: TEXT AS name, count(tag_id) AS posts_count
  FROM (SELECT id, unnest(tag_ids) AS tag_id FROM posts_lists) AS l
  GROUP BY tag_id;
ALTER TABLE tags
  ADD PRIMARY KEY (id);
CREATE UNIQUE INDEX tags_idx_name
  ON tags (name);
200 rows affected

CREATE OR REPLACE FUNCTION post_ids_sortby_score(tag_id INT, post_id INT, _count INT)
  RETURNS INT [] AS $$
DECLARE
  _t INT;
BEGIN
  IF tag_id = 0
  THEN
    IF post_id = 0
    THEN
      RETURN ARRAY(SELECT id FROM posts_lists ORDER BY score DESC, id DESC LIMIT _count);
    ELSE
      RETURN ARRAY(SELECT id
                   FROM posts_lists
                   WHERE (score, id) < (SELECT score, id FROM posts_lists WHERE id = post_id)
                   ORDER BY score DESC, id DESC
                   LIMIT _count);
    END IF;
  ELSE
    _t = (SELECT posts_count FROM tags WHERE id = tag_id FOR UPDATE);
    IF _t ISNULL OR _t = 0
    THEN
      RETURN NULL;
    ELSE
      IF post_id = 0
      THEN
        IF _t >= _count
        THEN
          RETURN ARRAY(
              SELECT id FROM posts_lists WHERE tag_ids @> ARRAY [tag_id] ORDER BY score DESC, id DESC LIMIT _count);
        ELSE
          RETURN ARRAY(SELECT id FROM posts_lists ORDER BY score DESC, id DESC);
        END IF;
      ELSE
        IF _t >= _count
        THEN
          RETURN ARRAY(
              SELECT id
              FROM posts_lists
              WHERE tag_ids @> ARRAY [tag_id]
                AND (score, id) < (SELECT score, id FROM posts_lists WHERE id = post_id)
              ORDER BY score DESC, id DESC
              LIMIT _count);
        ELSE
          RETURN ARRAY(SELECT id
                       FROM posts_lists
                       WHERE tag_ids @> ARRAY [tag_id]
                         AND (score, id) < (SELECT score, id FROM posts_lists WHERE id = post_id)
                       ORDER BY score DESC, id DESC);
        END IF;
      END IF;
    END IF;
  END IF;
END;
$$
LANGUAGE 'plpgsql';
EXPLAIN (ANALYZE, BUFFERS) SELECT ARRAY(
                                    SELECT id
                                    FROM posts_lists
                                    WHERE tag_ids @> ARRAY [1]
                                    ORDER BY score DESC, id DESC
                                    LIMIT 100); -- hit posts_lists_idx_tag_ids


QUERY PLAN


Result  (cost=796.74..796.75 rows=1 width=32) (actual time=2.357..2.357 rows=1 loops=1)

  Buffers: shared hit=202 read=380

  InitPlan 1 (returns $0)

    ->  Limit  (cost=796.49..796.74 rows=100 width=8) (actual time=2.318..2.340 rows=100 loops=1)

          Buffers: shared hit=202 read=380

          ->  Sort  (cost=796.49..797.74 rows=500 width=8) (actual time=2.317..2.326 rows=100 loops=1)

                Sort Key: score DESC, id DESC

                Sort Method: top-N heapsort  Memory: 32kB

                Buffers: shared hit=202 read=380

                ->  Bitmap Heap Scan on posts_lists  (cost=15.87..777.38 rows=500 width=8) (actual time=0.210..2.085 rows=964 loops=1)

                      Recheck Cond: (tag_ids @> '{1}'::integer[])

                      Heap Blocks: exact=579

                      Buffers: shared hit=202 read=380

                      ->  Bitmap Index Scan on posts_lists_idx_tag_ids  (cost=0.00..15.75 rows=500 width=0) (actual time=0.137..0.137 rows=964 loops=1)

                            Index Cond: (tag_ids @> '{1}'::integer[])

                            Buffers: shared hit=3

Planning time: 0.159 ms

Execution time: 2.401 ms


EXPLAIN (ANALYZE, BUFFERS) SELECT ARRAY(
                                    SELECT id
                                    FROM posts_lists
                                    WHERE tag_ids @> ARRAY [1]
                                    ORDER BY score DESC, id DESC
                                    LIMIT 25); -- hit posts_lists_idx_score_id


QUERY PLAN


Result  (cost=309.79..309.80 rows=1 width=32) (actual time=3.313..3.313 rows=1 loops=1)

  Buffers: shared hit=1339 read=648 written=259

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.29..309.79 rows=25 width=8) (actual time=0.041..3.304 rows=25 loops=1)

          Buffers: shared hit=1339 read=648 written=259

          ->  Index Scan using posts_lists_idx_score_id on posts_lists  (cost=0.29..6190.29 rows=500 width=8) (actual time=0.040..3.296 rows=25 loops=1)

                Filter: (tag_ids @> '{1}'::integer[])

                Rows Removed by Filter: 2057

                Buffers: shared hit=1339 read=648 written=259

Planning time: 0.072 ms

Execution time: 3.327 ms


SELECT * FROM tags WHERE id = 1;


id
name
posts_count


1
1
964


EXPLAIN ANALYZE SELECT post_ids_sortby_score(1, 0, 10000); --test < limit size


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=32) (actual time=49.824..49.825 rows=1 loops=1)

Planning time: 0.007 ms

Execution time: 49.871 ms


SELECT post_ids_sortby_score(1, 35802, 25);


post_ids_sortby_score


{48317,83455,25986,76165,81253,84846,49753,23506,95409,88463,52932,32923,88611,41731,39647,70029,40991,37671,10092,54652,19372,753,86422,64502,94950}


EXPLAIN ANALYZE SELECT post_ids_sortby_score(1, 35802, 25);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=32) (actual time=1.840..1.841 rows=1 loops=1)

Planning time: 0.009 ms

Execution time: 1.849 ms


SELECT post_ids_sortby_score(-1, 0, 10000);


post_ids_sortby_score


null


EXPLAIN ANALYZE SELECT post_ids_sortby_score(-1, 0, 10000); --no result


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=1)

Planning time: 0.006 ms

Execution time: 0.089 ms


SELECT post_ids_sortby_score(-1, 35802, 25);


post_ids_sortby_score


null


EXPLAIN ANALYZE SELECT post_ids_sortby_score(-1, 35802, 25); --no result


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=1)

Planning time: 0.006 ms

Execution time: 0.047 ms


SELECT pg_size_pretty(pg_table_size('posts_lists_idx_score_id'))  AS idx_score_id_size,
       pg_size_pretty(pg_table_size('posts_lists_idx_tag_ids'))   AS idx_tag_ids_size;


idx_score_id_size
idx_tag_ids_size


2208 kB
472 kB