Missing indexes and all_month_events performance

[imposeren]

1. It would be great to have at least basic indexes on start_date/end_date/(start_date,end_date). I have created such indexes for my setup and they seem to be used:

            tablename            |                            indexname                            | num_rows | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched 
---------------------------------+-----------------------------------------------------------------+----------+------------+------------+--------+-----------------+-------------+----------------
 happenings_event                | happenings_event_dates_idx                                      |     1010 | 176 kB     | 48 kB      | N      |              43 |          43 |             43
 happenings_event                | happenings_event_end_date_idx                                   |     1010 | 176 kB     | 40 kB      | N      |              26 |          26 |             26
 happenings_event                | happenings_event_start_date_idx                                 |     1010 | 176 kB     | 40 kB      | N      |               7 |         411 |            411

happenings_event_dates_idx is for index_together = (('start_date', 'end_date'),)
2. index for EventManager.all_month_events(year, month) would be great too but this seems to be impossible because of __month queries and general complexity of query:

explain (analyze) SELECT DISTINCT "happenings_event"."id", "happenings_event"."start_date", "happenings_event"."end_date", "happenings_event"."all_day", "happenings_event"."repeat", "happenings_event"."end_repeat", "happenings_event"."title", "happenings_event"."description", "happenings_event"."created_by_id", "happenings_event"."background_color", "happenings_event"."background_color_custom", "happenings_event"."font_color", "happenings_event"."font_color_custom" FROM "happenings_event" WHERE ((("happenings_event"."repeat" = 'YEARLY' AND (EXTRACT('month' FROM "happenings_event"."start_date" AT TIME ZONE 'Europe/Kiev') = 1 OR EXTRACT('month' FROM "happenings_event"."end_date" AT TIME ZONE 'Europe/Kiev') = 1)) OR NOT ("happenings_event"."repeat" = 'NEVER') OR (("happenings_event"."start_date" BETWEEN '2016-01-01T00:00:00+02:00'::timestamptz AND '2016-12-31T23:59:59.999999+02:00'::timestamptz OR "happenings_event"."end_date" BETWEEN '2016-01-01T00:00:00+02:00'::timestamptz AND '2016-12-31T23:59:59.999999+02:00'::timestamptz) AND (EXTRACT('month' FROM "happenings_event"."start_date" AT TIME ZONE 'Europe/Kiev') = 1 OR "happenings_event"."end_date" BETWEEN '2016-01-01T00:00:00+02:00'::timestamptz AND '2016-12-31T23:59:59.999999+02:00'::timestamptz))) AND ("happenings_event"."end_repeat" IS NULL OR "happenings_event"."end_repeat" >= '2016-01-01'::date) AND "happenings_event"."start_date" <= '2016-01-31T23:59:59.999999+02:00'::timestamptz) ORDER BY "happenings_event"."start_date" ASC;

QUERY PLAN


----------------------------------------------------------------------------------------------------
 Unique  (cost=80.22..80.53 rows=9 width=135) (actual time=0.637..0.679 rows=59 loops=1)
   ->  Sort  (cost=80.22..80.24 rows=9 width=135) (actual time=0.637..0.642 rows=59 loops=1)
         Sort Key: start_date, id, end_date, all_day, repeat, end_repeat, title, description, created_by_id, background_color, background_color_custom, font_color, font_color_custom
         Sort Method: quicksort  Memory: 37kB
         ->  Seq Scan on happenings_event  (cost=0.00..80.08 rows=9 width=135) (actual time=0.465..0.598 rows=59 loops=1)
               Filter: (((end_repeat IS NULL) OR (end_repeat >= '2016-01-01'::date)) AND (start_date <= '2016-01-31 16:59:59.999999-05'::timestamp with time zone) AND ((((repeat)::text = 'YEARLY'::text) AND ((date_part('month'::text, timezone('Europe/Kiev'::text, start_date)) = 1::double precision) OR (date_part('month'::text, timezone('Europe/Kiev'::text, end_date)) = 1::double precision))) OR ((repeat)::text <> 'NEVER'::text) OR ((((start_date >= '2015-12-31 17:00:00-05'::timestamp with time zone) AND (start_date <= '2016-12-31 16:59:59.999999-05'::timestamp with time zone)) OR ((end_date >= '2015-12-31 17:00:00-05'::timestamp with time zone) AND (end_date <= '2016-12-31 16:59:59.999999-05'::timestamp with time zone))) AND ((date_part('month'::text, timezone('Europe/Kiev'::text, start_date)) = 1::double precision) OR ((end_date >= '2015-12-31 17:00:00-05'::timestamp with time zone) AND (end_date <= '2016-12-31 16:59:59.999999-05'::timestamp with time zone))))))
               Rows Removed by Filter: 951
 Planning time: 0.334 ms
 Execution time: 1.026 ms
(9 rows)