How to create an index to be picked by query I have created multiple indexes one after another, but my query doesn't want to pick them :(

CREATE INDEX x 
  ON gan.paid (project_id, country_iso_code, source, date(created_at));
CREATE INDEX x 
  ON gan.paid (project_id, country_iso_code, text(source), date(created_at));
CREATE INDEX x 
  ON gan.paid (project_id, text(country_iso_code), text(source), date(created_at));


Query Plan:

WindowAgg  (cost=46024.07..52771.64 rows=24252 width=63) (actual time=3038.104..3072.217 rows=99983 loops=1)
 -&gt;  GroupAggregate  (cost=46024.07..52468.49 rows=24252 width=63) (actual time=2284.136..2982.067 rows=99983 loops=1)
    Group Key: key
    -&gt;  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
          Sort Key: key
          Sort Method: external merge  Disk: 13960kB
          -&gt;  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
                Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at &lt;= (now())::date) AND (created_at &gt;= ((now())::date - 100)))
                Rows Removed by Filter: 214895
Planning time: 0.233 ms
Execution time: 3082.612 ms


Query:

SELECT sum(views) vies
     , sum(likes) likes
     , key
     , count(*) OVER() full_count
FROM gan.paid
WHERE project_id = 1 
    AND country_iso_code = 'gb' 
    AND source = 'website' 
    AND created_at::date &gt;= now()::date - 100 
    AND created_at::date &lt;= now()::date
GROUP BY key;


Table definition:

CREATE TABLE gan.paid (
    id               integer pk
    key              text
    project_id       integer
    source           varchar
    country_iso_code varchar
    visits           integer
    likes            integer
    created_at       date
);


How can I create an index that will speed up this query?
This is the part of the execution plan where you expect an index being used:

      -&gt;  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
            Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at &lt;= (now())::date) AND (created_at &gt;= ((now())::date - 100)))
            Rows Removed by Filter: 214895


What we have to look at is the number of rows this node returns (198575) and the number it does not return (Rows Removed by Filter: 214895).  This means that the conditions in your WHERE clause are not really selective - they specify nearly half of the whole table.  

This is a situation where indexes seldom help - they usually said to kick in when the percentage of the returned rows is estimated to be under 10% (others say less, like 5%).

You can try the same query with a more selective set of conditions (practically, setting a narrower date range).  In this case, an index may help - the first one you list above, for example.  Depending on the distribution of the data (the different values of project_id, source, and country_iso_code, and the proportion of all rows having the specific values), a different order of the columns might be preferable (for example, created_at::date as the first column).  If the other columns are not selective enough in any case (for example, all rows have a project ID of 1), it can happen that you need created_at::date only.

At the same time, your problem seems not to be the (lack of) indexes, but that the query is sorting too much data.  This node:

-&gt;  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
      Sort Key: key
      Sort Method: external merge  Disk: 13960kB


shows that the work_mem setting is too low - the query needs at least 13960 kB more memory for this sort.  Do a 

SET work_mem TO xMB;


where x is {your current setting} + 16 MB.  This will spare you about 2 seconds.

Notes:


casting varchar to text does not help with your indexes.
Is this query just a part of a bigger picture?  If it isn't, I'd think a bit more about what I am planning to do with the nearly 100k rows returned.  It might mean a (slight) re-desing of your logic.