Thread: BUG #18978: Index does not work in aggregate function filter

The following bug has been logged on the website:

Bug reference:      18978
Logged by:          Jinhui
Email address:      jinhui-lai@foxmail.com
PostgreSQL version: 17.5
Operating system:   ubuntu 22.04
Description:

Dear PG developers,
Thanks for reading my report!
I would like to say that "aggregate_function(column) FILTER (WHERE
condition)" is really a wonderful feature in PG. For example,  "SELECT
COUNT(c1) AS total, COUNT(order_id) FILTER (WHERE c1<2) AS filtered FROM
t1", this query helps users get both total and filtered count.
However, when users wanna to add an index to speed up such queries, it
doesn't seem to work. You can reproduce it as follows:
CREATE TABLE t1(c1 INT8);
INSERT INTO t1 SELECT i FROM generate_series(1, 100000000) AS i;
CREATE INDEX i ON t1(c1);
SELECT COUNT(c1) FILTER (WHERE c1<2) FROM t1;
 count
-------
     1
(1 row)
Time: 3133.225 ms (00:03.133)
SELECT COUNT(c1) FROM t1 WHERE c1<2;
 count
-------
     1
(1 row)
Time: 3.756 ms
explain SELECT COUNT(c1) FILTER (WHERE c1<2) FROM t1;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1068478.22..1068478.23 rows=1 width=8)
   ->  Gather  (cost=1068478.00..1068478.21 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=1067478.00..1067478.01 rows=1 width=8)
               ->  Parallel Seq Scan on t1  (cost=0.00..859144.67
rows=41666667 width=8)
 JIT:
   Functions: 5
   Options: Inlining true, Optimization true, Expressions true, Deforming
true
explain SELECT COUNT(c1) FROM t1 WHERE c1<2;
                              QUERY PLAN
-----------------------------------------------------------------------
 Aggregate  (cost=4.59..4.60 rows=1 width=8)
   ->  Index Only Scan using i on t1  (cost=0.57..4.58 rows=1 width=8)
         Index Cond: (c1 < 2)
Do you think this great feature of PG needs to be optimized?
Thanks you once again. I look forward to your reply.
Best regard,
Jinhui