Postgres: performance difference between functional and attribute notation when doing qualified count of rows

Question

I have a large table, which I want to get a count of some subset. The filter parameter is on a column that has an index on it. If I was just writing a normal query, I'd do it like so:

SELECT count(*) FROM foo WHERE ts > '2022-07-01';

However, it's possible to use attribute notation with the count function:

SELECT foo.count FROM foo WHERE ts > '2022-07-01';

My understanding is that second version should be semantically identical to the first. However, in practice it results in a different query plan with significantly worse performance characteristics (parallel index-only scan vs parallel seq scan).

I'm unfortunately locked into the second version, because it's how PostgREST implements count queries. The situation is actually worse than this toy example, because in most cases I'm selecting against a view, not a table, and in the functional ('normal') case, the query planner is smart enough to use indices, whereas in the attribute case, it looks like the planner executes the entirety of the view (e.g. approximately identical performance as doing SELECT * FROM myview WHERE ts > '2022-07-01') and then just counts the result set. In situations where some columns are derived from moderately expensive inline function calls, this is the difference between a query running in 2.5 seconds vs 2.5 minutes.

I'm interested in

1. understanding why these two apparently-equivalent queries produce different query plans, and
2. if there's a way of nudging the query planner to use the more-optimised plan for the attribute-notated form of the query.

---

Minimal reproduction:

CREATE TABLE bar (id PRIMARY KEY)

CREATE TABLE foo (
  id     bigint      PRIMARY KEY, 
  ts     timestamptz UNIQUE,
  -- for whatever reason I can only get a replication if I add a foreign key. 
  -- Not sure if this is significant...
  bar_id bigint REFERENCES bar(id)
);

INSERT INTO bar (id) SELECT generate_series(1, 1e6);

WITH t AS (
  select * 
  from generate_series('2022-01-01 00:00'::timestamptz, '2022-12-31 23:59:59', '1 second') ts
) 
INSERT INTO foo (id, ts, bar_id) 
SELECT rank, ts, CASE WHEN rank <= 1e6 THEN rank ELSE NULL END
FROM (SELECT rank() over (order by ts), ts FROM t) a
;

EXPLAIN ANALYZE SELECT count(*) FROM foo WHERE ts > '2022-07-01';

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                             QUERY PLAN                                                                             │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate  (cost=587875.34..587875.35 rows=1 width=8) (actual time=4789.976..4802.692 rows=1 loops=1)                                                     │
│   ->  Gather  (cost=587875.13..587875.34 rows=2 width=8) (actual time=4789.778..4802.645 rows=3 loops=1)                                                           │
│         Workers Planned: 2                                                                                                                                         │
│         Workers Launched: 2                                                                                                                                        │
│         ->  Partial Aggregate  (cost=586875.13..586875.14 rows=1 width=8) (actual time=4740.529..4740.530 rows=1 loops=3)                                          │
│               ->  Parallel Index Only Scan using foo_ts_key on foo  (cost=0.56..563750.57 rows=9249822 width=0) (actual time=0.856..4503.148 rows=5212800 loops=3) │
│                     Index Cond: (ts < '2022-07-01 00:00:00+00'::timestamp with time zone)                                                                          │
│                     Heap Fetches: 24110439                                                                                                                         │
│ Planning Time: 0.344 ms                                                                                                                                            │
│ JIT:                                                                                                                                                               │
│   Functions: 11                                                                                                                                                    │
│   Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                      │
│   Timing: Generation 3.898 ms, Inlining 77.703 ms, Optimization 30.218 ms, Emission 20.267 ms, Total 132.086 ms                                                    │
│ Execution Time: 4806.155 ms                                                                                                                                        │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT foo.count FROM foo WHERE ts > '2022-07-01';

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                  QUERY PLAN                                                                  │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate  (cost=721287.60..721287.61 rows=1 width=8) (actual time=2051.126..2059.868 rows=1 loops=1)                               │
│   ->  Gather  (cost=721287.38..721287.59 rows=2 width=8) (actual time=2051.004..2059.858 rows=3 loops=1)                                     │
│         Workers Planned: 2                                                                                                                   │
│         Workers Launched: 2                                                                                                                  │
│         ->  Partial Aggregate  (cost=720287.38..720287.39 rows=1 width=8) (actual time=2020.404..2020.405 rows=1 loops=3)                    │
│               ->  Parallel Seq Scan on foo  (cost=0.00..697162.83 rows=9249822 width=81) (actual time=49.001..1761.162 rows=5212800 loops=3) │
│                     Filter: (ts < '2022-07-01 00:00:00+00'::timestamp with time zone)                                                        │
│                     Rows Removed by Filter: 5299200                                                                                          │
│ Planning Time: 0.403 ms                                                                                                                      │
│ JIT:                                                                                                                                         │
│   Functions: 17                                                                                                                              │
│   Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                │
│   Timing: Generation 2.818 ms, Inlining 76.615 ms, Optimization 38.902 ms, Emission 30.954 ms, Total 149.289 ms                              │
│ Execution Time: 2061.402 ms                                                                                                                  │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Note that in this minimal reproduction the execution time is actually better for the seq scan example, but it serves to demonstrate that different query plans can be generated, and when I try on a real database, the index-based plan tends to be drastically better.

Answer 1

The documentation describes that foo.count and count(foo) are actually the same:

A function that takes a single argument of composite type can optionally be called using field-selection syntax, and conversely field selection can be written in functional style. That is, the notations col(table) and table.col are interchangeable. This behavior is not SQL-standard but is provided in PostgreSQL because it allows use of functions to emulate “computed fields”.

The observed difference is just the difference between count(*) and count(foo). They are different. You are probably misled by * being an abbreviation for “all columns” in SELECT lists. But the * in aggregate functions is something different: it is the SQL standard's quirky way ow writing an aggregate function call without parameters. Compare this PostgreSQL error message:

SELECT count();
ERROR:  count(*) must be used to call a parameterless aggregate function
LINE 1: SELECT count();
               ^

While count(*) counts the rows, count(foo) counts the rows where foo IS NOT NULL. So the latter cannot use an index-only scan, because it has to retrieve the row and check if it is NULL or not.