Re: Filtering before join with date_trunc()

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.