Can aggregate filter expressions not use indexes?

Question

Can aggregate filter expressions not use indexes?

One interesting thing about filter expressions is that you can execute several different filters and aggregates in a single query. The where part becomes part of the aggregation, not the whole where clause.

For instance:

SELECT count('id') FILTER (WHERE account_type=1) as regular,
       count('id') FILTER (WHERE account_type=2) as gold,
       count('id') FILTER (WHERE account_type=3) as platinum
FROM clients;

(from Django documentation )

Either this is a bug in PostgreSQL 9.5, or I'm not doing it right, it's just a limitation of PostgreSQL.

Consider these two questions:

select count(*)
from main_search
where created >= '2017-10-12T00:00:00.081739+00:00'::timestamptz
and created < '2017-10-13T00:00:00.081739+00:00'::timestamptz
and parent_id is null;

select
count('id') filter (
where created >= '2017-10-12T00:00:00.081739+00:00'::timestamptz
and created < '2017-10-13T00:00:00.081739+00:00'::timestamptz
and parent_id is null) as count
from main_search;

(The table main_searchhas a btree combined index of created and parent_id is null)

Here's the conclusion:

 count
-------
  9682
(1 row)

 count
-------
  9682
(1 row)

If I stick explain analyzewith each request, this is the output:

    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1174.04..1174.05 rows=1 width=0) (actual time=5.077..5.077 rows=1 loops=1)
   ->  Index Scan using main_search_created_parent_id_null_idx on main_search  (cost=0.43..1152.69 rows=8540 width=0) (actual time=0.026..4.384 rows=9682 loops=1)
         Index Cond: ((created >= '2017-10-11 20:00:00.081739-04'::timestamp with time zone) AND (created < '2017-10-12 20:00:00.081739-04'::timestamp with time zone))
 Planning time: 0.826 ms
 Execution time: 5.227 ms
(5 rows)

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=178054.93..178054.94 rows=1 width=12) (actual time=1589.006..1589.007 rows=1 loops=1)
   ->  Seq Scan on main_search  (cost=0.00..146459.39 rows=4212739 width=12) (actual time=0.051..882.099 rows=4212818 loops=1)
 Planning time: 0.051 ms
 Execution time: 1589.070 ms
(4 rows)