#1 CREATE INDEX tbl_idx ON tbl (date_column) SELECT COUNT() FROM tbl WHERE EXTRACT(YEAR FROM date_column) = 2017
I've never used EXTRACT() in my life, so I don't know if it's index aware. I do know in real life I would write "WHERE date_column >= '2017-1-1' AND date_column <= '2017-12-31' or if I were querying a school_year or something that spanned between two years I would add another column and probably an index on that column and query by that not by the date_column.
#2 CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT FROM tbl WHERE a = 12 ORDER BY date_column DESC FETCH FIRST 1 ROW ONLY
Who uses FETCH FIRST 1 ROW ONLY instead of LIMIT 1? Also indexes can be ordered by ASC or DESC so there's a possibility of a small optimization there.
#4 CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops)
SELECT * FROM tbl WHERE text LIKE 'TJ%'
I thought the general philosophy regarding indexes was create the ones you know you need like on foreign keys and very common ones like last_name, SSN etc and then if you notice queries running slow add more and test. This seems like one of those examples. Do you really need an index here? How often are you making this query and what's the speedup gained if you add an index?
#5 CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count() FROM tbl WHERE a = 38 GROUP BY date_column
Let's say this query returns at least a few rows.
To implement a new functional requirement, another condition (b = 1) is added to the where clause:
SELECT date_column, count() FROM tbl WHERE a = 38 AND b = 1 GROUP BY date_column
This seems like another case in the real world where you might test what the slowdown is, and how often you're running this query. If needed you can add another index or run a subquery first, but the answer to this question can be found out in < 10seconds in the real world more quickly by testing it than even spending the time thinking about it.