Optimizing a Query

Question

I have to run this query, on Postgres 9.4.8 and it's insanely slow, likely because the page_views table contains about 200mil records.

Here's the query:

EXPLAIN ANALYZE 
SELECT COUNT(*) AS count_all, 
       date(created_at) AS date_created_at 
FROM "page_views"  
WHERE "page_views"."feed_id" = 20142 
AND (created_at between '2016-07-03 18:18:19.665009' and '2016-08-02 18:18:23.144754') 
GROUP BY date(created_at);

And the ANALYZE EXPLAIN results:

    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=30491.20..30492.43 rows=353 width=8) (actual time=294668.397..294668.403 rows=30 loops=1)
   Group Key: date(created_at)
   ->  Bitmap Heap Scan on page_views  (cost=29499.12..30490.70 rows=498 width=8) (actual time=292960.060..294667.729 rows=1011 loops=1)
         Recheck Cond: ((feed_id = 20142) AND (created_at >= '2016-07-03 18:18:19.665009'::timestamp without time zone) AND (created_at <= '2016-08-02 18:18:23.144754'::timestamp without time zone))
         Heap Blocks: exact=998
         ->  BitmapAnd  (cost=29499.12..29499.12 rows=498 width=0) (actual time=292959.413..292959.413 rows=0 loops=1)
               ->  Bitmap Index Scan on index_page_views_on_feed_id  (cost=0.00..245.12 rows=30003 width=0) (actual time=0.312..0.312 rows=1839 loops=1)
                     Index Cond: (feed_id = 20142)
               ->  Bitmap Index Scan on index_page_views_on_created_at  (cost=0.00..29253.90 rows=3516895 width=0) (actual time=292956.885..292956.885 rows=26220273 loops=1)
                     Index Cond: ((created_at >= '2016-07-03 18:18:19.665009'::timestamp without time zone) AND (created_at <= '2016-08-02 18:18:23.144754'::timestamp without time zone))
 Planning time: 0.144 ms
 Execution time: 294668.450 ms

Here's the definition of the table.

 \d+ page_views;
                                                         Table "public.page_views"
   Column   |            Type             |                        Modifiers                        | Storage  | Stats target | Description
------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------
 id         | integer                     | not null default nextval('page_views_id_seq'::regclass) | plain    |              |
 feed_id    | integer                     |                                                         | plain    |              |
 url        | text                        |                                                         | extended |              |
 created_at | timestamp without time zone |                                                         | plain    |              |
 updated_at | timestamp without time zone |                                                         | plain    |              |
Indexes:
    "page_views_pkey" PRIMARY KEY, btree (id)
    "index_page_views_on_created_at" btree (created_at DESC)
    "index_page_views_on_feed_id" btree (feed_id)
Has OIDs: no

Any recommendations as to how to go about speeding this up?

* EDIT *

I've run VACUUM ANALYZE page_views; which took forever and then ran the ANALYZE EXPLAIN again:

    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=44520.59..44532.91 rows=3520 width=8) (actual time=14315.883..14315.893 rows=30 loops=1)
   Group Key: date(created_at)
   ->  Index Scan using index_page_views_on_feed_id on page_views  (cost=0.11..44517.07 rows=3520 width=8) (actual time=160.623..14314.562 rows=1011 loops=1)
         Index Cond: (feed_id = 20142)
         Filter: ((created_at >= '2016-07-03 18:18:19.665009'::timestamp without time zone) AND (created_at <= '2016-08-02 18:18:23.144754'::timestamp without time zone))
         Rows Removed by Filter: 832
 Planning time: 0.609 ms
 Execution time: 14315.981 ms
(8 rows)

Answer 1

it looks like the feed is much more restrictive and only returns ~2k records, and most of the time is spent on building/filtering the time.

If this is usually the case, consider extracting it to a CTE and only filter the time on the returned tuples - it will only need to scan the 2k records and would probably avoid the index.

Regards Jony