I know this question must come up often, but I can't seem to find a recent answer that addresses something more simple like what I'm trying to do. I've read several similar questions.
I'm using PostgreSQL 9.4 and have 2 tables, each with close to 200M rows containing two types of metrics. The columns are the_date, feature_A, feature_B and 24 hourly metrics. The metrics in the two tables are different enough such that the tables can't be combined. Or at least I don't think so. Some metrics don't apply to all features and that's why they're separated. Both tables have indexes on the date column.
When I try to do a query that joins them, if I specify a single date the query will return in about 3 seconds which seems reasonable to me. But if I simply do a.the_date between '8/1/2017' and '8/1/2017' the query now takes essentially forever. I kill it after 10 minutes because I'm impatient. Obviously PostgreSQL is no longer using the indexes.
Why is that and how can I get it to use the indexes again? I'd like to do queries like a.the_date between '7/1/2017' and '7/31/2017' or a.the_date >= '1/1/2017'. Right now I've simply got a python program running the query for one date at a time and then totaling the results, which seems like a hack. But at least it's predictable and much faster at 3-seconds-per-date.
The query:
select a.feature_a, a.feature_b, count(*) tot_ct,
count(case when b.br01 > 0 and a.bc01 <= 10 then 1 end) cx01,
count(case when b.br02 > 0 and a.bc02 <= 10 then 1 end) cx02,
count(case when b.br03 > 0 and a.bc03 <= 10 then 1 end) cx03,
count(case when b.br04 > 0 and a.bc04 <= 10 then 1 end) cx04,
count(case when b.br05 > 0 and a.bc05 <= 10 then 1 end) cx05,
count(case when b.br06 > 0 and a.bc06 <= 10 then 1 end) cx06,
count(case when b.br07 > 0 and a.bc07 <= 10 then 1 end) cx07,
count(case when b.br08 > 0 and a.bc08 <= 10 then 1 end) cx08,
count(case when b.br09 > 0 and a.bc09 <= 10 then 1 end) cx09,
count(case when b.br10 > 0 and a.bc10 <= 10 then 1 end) cx10,
count(case when b.br11 > 0 and a.bc11 <= 10 then 1 end) cx11,
count(case when b.br12 > 0 and a.bc12 <= 10 then 1 end) cx12,
count(case when b.br13 > 0 and a.bc13 <= 10 then 1 end) cx13,
count(case when b.br14 > 0 and a.bc14 <= 10 then 1 end) cx14,
count(case when b.br15 > 0 and a.bc15 <= 10 then 1 end) cx15,
count(case when b.br16 > 0 and a.bc16 <= 10 then 1 end) cx16,
count(case when b.br17 > 0 and a.bc17 <= 10 then 1 end) cx17,
count(case when b.br18 > 0 and a.bc18 <= 10 then 1 end) cx18,
count(case when b.br19 > 0 and a.bc19 <= 10 then 1 end) cx19,
count(case when b.br20 > 0 and a.bc20 <= 10 then 1 end) cx20,
count(case when b.br21 > 0 and a.bc21 <= 10 then 1 end) cx21,
count(case when b.br22 > 0 and a.bc22 <= 10 then 1 end) cx22,
count(case when b.br23 > 0 and a.bc23 <= 10 then 1 end) cx23,
count(case when b.br24 > 0 and a.bc24 <= 10 then 1 end) cx24,
avg(b.br01) av01, avg(b.br02) av02, avg(b.br03) av03,
avg(b.br04) av04, avg(b.br05) av05, avg(b.br06) av06,
avg(b.br07) av07, avg(b.br08) av08, avg(b.br09) av09,
avg(b.br10) av10, avg(b.br11) av11, avg(b.br12) av12,
avg(b.br13) av13, avg(b.br14) av14, avg(b.br15) av15,
avg(b.br16) av16, avg(b.br17) av17, avg(b.br18) av18,
avg(b.br19) av19, avg(b.br20) av20, avg(b.br21) av21,
avg(b.br22) av22, avg(b.br23) av23, avg(b.br24) av24
from table_a a, table_b b
where a.the_date = '8/1/2017'
and a.the_date = b.the_date
and a.feature_a = b.feature_a
and a.feature_b = b.feature_b
group by a.feature_a, a.feature_b
Update:
EXPLAIN on the above query:
GroupAggregate (cost=3123833.79..5157342.05 rows=201 width=680)
Group Key: a.feature_a, a.feature_b
-> Merge Join (cost=3123833.79..3220488.32 rows=5270312 width=680)
Merge Cond: ((a.feature_a = b.feature_a) AND (a.feature_b = b.feature_b))
-> Sort (cost=1438291.32..1439351.90 rows=424234 width=348)
Sort Key: a.feature_a, a.feature_b
-> Bitmap Heap Scan on table_a a (cost=14960.38..1262333.10 rows=424234 width=348)
Recheck Cond: (the_day = '2017-08-01'::date)
-> Bitmap Index Scan on table_a_day_idx (cost=0.00..14854.32 rows=424234 width=0)
Index Cond: (the_day = '2017-08-01'::date)
-> Materialize (cost=1685542.47..1688027.10 rows=496925 width=348)
-> Sort (cost=1685542.47..1686784.79 rows=496925 width=348)
Sort Key: b.feature_a, b.feature_b
-> Bitmap Heap Scan on table_b b (cost=17759.74..1478863.74 rows=496925 width=348)
Recheck Cond: (the_day = '2017-08-01'::date)
-> Bitmap Index Scan on table_b_day_idx (cost=0.00..17635.51 rows=496925 width=0)
Index Cond: (the_day = '2017-08-01'::date)
EXPLAIN on date-range query (8/1 to 8/5) not using table_b index:
GroupAggregate (cost=84467702.82..87504872.78 rows=201 width=680)
Group Key: a.feature_a, a.feature_b
-> Merge Join (cost=84467702.82..85568019.42 rows=5270311 width=680)
Merge Cond: ((b.feature_a = a.feature_a) AND (b.feature_b = a.feature_b) AND (b.the_day = a.the_day))
-> Sort (cost=83027290.33..83275752.78 rows=99384980 width=348)
Sort Key: b.feature_a, b.feature_b, b.the_day
-> Seq Scan on table_b b (cost=0.00..5963098.80 rows=99384980 width=348)
-> Materialize (cost=1440412.49..1442533.66 rows=424234 width=348)
-> Sort (cost=1440412.49..1441473.07 rows=424234 width=348)
Sort Key: a.feature_a, a.feature_b, a.the_day
-> Bitmap Heap Scan on table_a a (cost=16020.97..1264454.27 rows=424234 width=348)
Recheck Cond: ((the_day >= '2017-08-01'::date) AND (the_day <= '2017-08-05'::date))
-> Bitmap Index Scan on table_a_day_idx (cost=0.00..15914.91 rows=424234 width=0)
Index Cond: ((the_day >= '2017-08-01'::date) AND (the_day <= '2017-08-05'::date))
