Not sure what kind of notation you are using in your question. It's not Postgres syntax. A proper setup could look like this:
db<>fiddle here
SQL Fiddle.Old sqlfiddle
More about this fiddle further down.
Assuming data type timestamptimestamp for the column datetime.
BETWEEN is almost always wrong on principal with timestamp columns. More details in this related answerSee:
SELECT o.one_id, date(o.cut_time), o.f1, t.f2
FROM one o
JOIN two t USING (one_id)
WHERE o.cut_time BETWEEN '2013-01-01' AND '2013-01-31';
SELECT o.one_id, date(o.cut_time), o.f1, t.f2
FROM one o
JOIN two t USING (one_id)
WHERE o.cut_time BETWEEN '2013-01-01' AND '2013-01-31';
... the string constants '2013-01-01' and '2013-01-31' are coerced to the timestamps '2013-01-01 00:00' and '2013-01-31 00:00'. This excludes most of Jan. 31. The timestamp '2013-01-31 12:00' would not qualify, which is most certainly wrong.
If you'd use '2013-02-01' as upper borderbound instead, it'dit would include '2013-02-01 00:00'. Still wrong.
Exclude the upper borderbound.
@Clodoaldo already mentioned the major drag on performance: it's It's probably pointless to retrieve 1.7 miomillion rows. Aggregate before you retrieve the result.
Since table two is so much bigger, theit's crucial are thehow many rows, you have to retrieveget from there. As long as you retrieve a large part of the table,When retrieving more than ~ 5% 5 %, a plain index on two.one_id will typically not be used, because it is faster to scan the table sequentially right away.
The only chance I would see for an index on two is a covering index with PostgreSQL 9.2. But you neglected to disclose your version number.:
CREATE INDEX two_one_id_f2 onON two(one_id, f2);
As to your strange numbers in your EXPLAIN ANALYZE. ThisThe SQL Fiddlefiddle should explain it.
All of them should be on (default setting), except for debugging. Would crippleElse it cripples performance! Check with:
SELECT * FROM pg_settings WHERE name ~~ 'enable%''enable%';
