4

I have the following database schema:

CREATE TABLE foo(
  id  SERIAL  PRIMARY KEY,
  tag INTEGER NOT NULL,
  val INTEGER NOT NULL
);
CREATE INDEX foo_tag ON foo(tag);
CREATE INDEX foo_val ON foo(val);

CREATE VIEW tag_min(tag, val) AS
  SELECT tag, MIN(val)
  FROM foo
  GROUP BY tag;

Here’s a query that inserts some dummy data that reproduces my problem:

INSERT INTO foo(tag, val)
SELECT random() * 50000 AS tag
     , random() *   100 AS val
FROM generate_series(1, 1000000);

ANALYZE;

The problem

I would like to make a sparse query on the tag_min view. That is, I want to obtain MIN(val) for a small handful of tag values. If I know the values ahead of time, I can do this very easily using a query such as the following:

SELECT val FROM tag_min WHERE tag IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

As expected, this query is extremely fast, as it essentially just performs a partial index scan for the ten tag values and aggregates them:

Subquery Scan on tag_min  (cost=744.01..748.13 rows=206 width=4) (actual time=0.708..0.710 rows=10 loops=1)
  ->  HashAggregate  (cost=744.01..746.07 rows=206 width=8) (actual time=0.707..0.709 rows=10 loops=1)
        Group Key: foo.tag
        Batches: 1  Memory Usage: 40kB
        ->  Bitmap Heap Scan on foo  (cost=45.90..742.98 rows=206 width=8) (actual time=0.045..0.671 rows=203 loops=1)
              Recheck Cond: (tag = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
              Heap Blocks: exact=199
              ->  Bitmap Index Scan on foo_tag  (cost=0.00..45.83 rows=206 width=0) (actual time=0.026..0.026 rows=203 loops=1)
                    Index Cond: (tag = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
Planning Time: 0.308 ms
Execution Time: 0.769 ms

However, in my real program, I do not have the tag values directly in hand. Instead, I obtain them via a join with another query. To keep the example as simple as possible, here’s an illustration using generate_series to represent the subquery (my real query is of course less contrived, but the behavior is the same):

SELECT val FROM generate_series(1, 10) n JOIN tag_min ON tag = n;

Surprisingly, this query performs enormously worse—over 500× worse! Here is the query plan:

Hash Join  (cost=0.65..47555.36 rows=2428 width=4) (actual time=0.062..405.515 rows=10 loops=1)
  Hash Cond: (foo.tag = n.n)
  ->  GroupAggregate  (cost=0.42..46863.22 rows=48555 width=8) (actual time=0.045..402.654 rows=50001 loops=1)
        Group Key: foo.tag
        ->  Index Scan using foo_tag on foo  (cost=0.42..41377.67 rows=1000000 width=8) (actual time=0.012..319.113 rows=1000000 loops=1)
  ->  Hash  (cost=0.10..0.10 rows=10 width=4) (actual time=0.009..0.010 rows=10 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Function Scan on generate_series n  (cost=0.00..0.10 rows=10 width=4) (actual time=0.005..0.006 rows=10 loops=1)
Planning Time: 0.075 ms
Execution Time: 405.533 ms

This query plan is terrible. The planner correctly estimates that the number of rows returned in the joined query is very small (exactly 10 in this example), but it still chooses to perform a hash join on a full index scan of tag_min instead of performing a partial index scan.

Perhaps this is not wildly surprising. After all, the first query used IN, but this query uses JOIN. However, rewriting my query to use IN does not actually change the query plan—the following query is executed essentially identically:

SELECT val FROM tag_min WHERE tag IN (SELECT n FROM generate_series(1, 10) n);

Other types of joins do not help, either. Even performing a correlated LATERAL subquery does not work! This query is executed in exactly the same way as the first one:

SELECT val FROM generate_series(1, 10) n, LATERAL
  (SELECT val FROM tag_min WHERE tag = n) tag_min;

An unsatisfying solution

The only way I have managed to force Postgres to use the better query plan is to perform a correlated subquery in result position, like this:

SELECT (SELECT val FROM tag_min WHERE tag = n) FROM generate_series(1, 10) n

This finally produces the query plan I want:

Function Scan on generate_series n  (cost=0.00..854.59 rows=10 width=4) (actual time=0.040..0.176 rows=10 loops=1)
  SubPlan 1
    ->  Subquery Scan on tag_min  (cost=4.59..85.45 rows=21 width=4) (actual time=0.017..0.017 rows=1 loops=10)
          ->  GroupAggregate  (cost=4.59..85.24 rows=21 width=8) (actual time=0.016..0.017 rows=1 loops=10)
                Group Key: foo.tag
                ->  Bitmap Heap Scan on foo  (cost=4.59..84.92 rows=21 width=8) (actual time=0.005..0.014 rows=20 loops=10)
                      Recheck Cond: (tag = n.n)
                      Heap Blocks: exact=203
                      ->  Bitmap Index Scan on foo_tag  (cost=0.00..4.58 rows=21 width=0) (actual time=0.003..0.003 rows=20 loops=10)
                            Index Cond: (tag = n.n)
Planning Time: 0.071 ms
Execution Time: 0.194 ms

However, I am somewhat wary of actually using this approach, for a few reasons:

  • It seems like a fairly heavy hammer, and I worry that it may deprive the query planner of making better choices if they are available in the future.

  • It is difficult to understand compared to the query using a JOIN.

  • It is very awkward to use this approach if I want to obtain multiple columns from the subquery. (Technically it is possible to do using composite types, but consuming the resulting values becomes annoying.)

Is there a better way to get Postgres to execute this query efficiently?

1
  • It seems that's a problem with the predicate pushdown on the view. When querying the table directly the plan looks good to me: explain.depesz.com/s/QJgt
    – user1822
    Apr 11, 2023 at 6:15

1 Answer 1

-1

You can rewrite your query to:

  SELECT tag, MIN(val)
  FROM foo, generate_series(1, 10) n
  where tag = n
  GROUP BY tag;

and you probably want to add an index on foo(tag, val), then you and avoid sorting and only fetch one index page for each tag value.

2
  • The query in my question is simplified. My real query is more complicated, and it joins with some other tables, so I cannot easily make it an aggregate query. But also, more broadly, my view is more complicated as well, and I would like to be able to query it so that I don’t have to duplicate its logic elsewhere. Apr 18, 2023 at 8:28
  • You need to post your real query and view definition if your question is about a specific problem. If you just need some general advice about how to materialize view result, you can achieve it by materializing view(you can do it by yourself using trigger if your database doesn't support materialized view). Apr 19, 2023 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.