I have a query that has several filter conditions after WHERE clause.
Also, most of the columns involved have indexes on them.
When I run EXPLAIN command, I see:
-> Bitmap Index Scan on feature_expr_idx (cost=0.00..8.10 rows=14 width=0)
feature_expr_idx is an index on one of the columns in WHERE clause.
But indexes for other columns are not shown. Instead, they are shown in FILTER row:
Filter: ((NOT is_deleted) AND (vehicle_type = 'car'::text) AND (source_type = 'NONE'::text))
Why only a single Index is shown in the result, while other columns also having index are instead part of Filter?
Postgresql has a clever engine which tries to plan the best way to run your query. Often, this involves reading as little as possible from disk, because disk operations are slow. One of the reasons why indexes are so helpful is that by reading from the index, we can find a small number of rows in the table that need to be read in order to satisfy the query, and thus we can avoid reading through the entire table. Note, however, that the index is also on disk, and so reading the index also takes some time.
Now, imagine your query has two filters, one over column A and one over column B, both of which are indexed. According to the statistics postgresql has collected, there are about 5 rows that satisfy the filter on column A, and about 1000 rows that satisfy the filter on column B. In that case, it makes sense to read only the index on column A, then read all the matching 5 (or so) rows, and filter out any of them which don't match the filter on column B. Reading the index on column B would probably be more expensive than just reading the 5 rows!
The actual reason may be different than my example, but the point is that postgresql is simply trying to be as efficient as possible.