Thanks all for the replies.
Tom Lane wrote:
> You're expecting too much.
That often seems to be the case.
> I think you're also expecting the system to deduce that it can apply an
> inequality on one join column to the other one. It doesn't; only equality
> constraints have any sort of transitivity logic.
>
> So you'll need to write out the BETWEEN separately for each table,
> and put it below the full join, which means you won't be able to
> use those nice views :-(
Here's an example:
create table t1 ("time" timestamptz, value1 numeric);
create index t1_time on t1("time");
\copy t1 from ......
create table t2 ("time" timestamptz, value2 numeric);
create index t2_time on t2("time");
\copy t2 from ......
explain select * from t1 join t2 using("time") where "time" between
'2018-10-01' and '2018-10-02';
+------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
+------------------------------------------------------------------------------------------------+
| Hash Join (cost=12.99..101.03 rows=138 width=21)
|
| Hash Cond: (t2."time" = t1."time")
|
| -> Seq Scan on t2 (cost=0.00..70.11 rows=4411 width=15)
|
| -> Hash (cost=11.18..11.18 rows=145 width=14)
|
| -> Index Scan using t1_time on t1 (cost=0.28..11.18 rows=145
width=14) |
| Index Cond: (("time" >= '2018-10-01 00:00:00+00'::timestamp
with time zone) AND .|
|.("time" <= '2018-10-02 00:00:00+00'::timestamp with time zone))
|
+------------------------------------------------------------------------------------------------+
explain with q1 as (select * from t1 where "time" between '2018-10-01' and
'2018-10-02'), q2 as (select * from t2 where "time" between '2018-10-01' and
'2018-10-02') select * from q1 join q2 using("time");
+------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
+------------------------------------------------------------------------------------------------+
| Hash Join (cost=26.60..31.41 rows=136 width=72)
|
| Hash Cond: (q1."time" = q2."time")
|
| CTE q1
|
| -> Index Scan using t1_time on t1 (cost=0.28..11.18 rows=145 width=14)
|
| Index Cond: (("time" >= '2018-10-01 00:00:00+00'::timestamp with
time zone) AND ("ti.|
|.me" <= '2018-10-02 00:00:00+00'::timestamp with time zone))
|
| CTE q2
|
| -> Index Scan using t2_time on t2 (cost=0.28..11.00 rows=136 width=15)
|
| Index Cond: (("time" >= '2018-10-01 00:00:00+00'::timestamp with
time zone) AND ("ti.|
|.me" <= '2018-10-02 00:00:00+00'::timestamp with time zone))
|
| -> CTE Scan on q1 (cost=0.00..2.90 rows=145 width=40)
|
| -> Hash (cost=2.72..2.72 rows=136 width=40)
|
| -> CTE Scan on q2 (cost=0.00..2.72 rows=136 width=40)
|
+------------------------------------------------------------------------------------------------+
So.... as you say, even if I strip out all of the complexity of approximate
timestamps and missing values, it's never going to push the BETWEEN filter
down below the join. Even with just a few thousand rows I see a 5X speedup
with the second query with the explicit filtering below the join.
This is rather disappointing. Am I the only person who's ever wanted to do
this?
Regards, Phil.
