Optimize date query for large child tables: GiST or GIN?

Answer 1

Try something like this:

create temporary table test (d date);

insert into test select '1970-01-01'::date+generate_series(1,50*365);

analyze test

create function month_day(d date) returns int as $$
  select extract(month from $1)::int*100+extract(day from $1)::int $$
language sql immutable strict;

create index test_d_month_day_idx on test (month_day(d));

explain analyze select * from test
  where month_day(d)>=month_day('2000-04-01')
  and month_day(d)<=month_day('2000-04-05');

Answer 2

Your problem is that you have a where clause depending on a calculation of the date. There is no way the database can use an index if it needs to fetch every row and do a calculation on it before knowing if the date will match.

Unless you rewrite it to be in the form where the database has a fixed range to check which does not depend on the data it is to retrieve you will always have to scan the table.

Answer 3

I think to run this efficiently across those partitions I would have your app be alot smarter about the date ranges. Have it generate an actual list of dates to check per partition and then have it generate one query with a UNION between the partitions. It sounds like your data set is pretty static, so a CLUSTER on your date index could greatly improve performance as well.