Re: Filtering before join with date_trunc()

On 10/15/18 8:57 AM, Phil Endecott wrote:
> Dear Experts,
>
> I have a few tables with "raw" timestamsps like this:
>
> +-------------------------------+----------+
> | time | pressure |
> +-------------------------------+----------+
> | 2018-09-14 00:00:07.148378+00 | 1007.52 |
> | 2018-09-14 00:10:07.147506+00 | 1007.43 |
> | 2018-09-14 00:20:07.147533+00 | 1007.28 |
> +-------------------------------+----------+
>
> For each of these tables I have a view which rounds the timestamp
> to the nearest minute, and ensures there is only one row per minute:
>
> SELECT date_trunc('minute'::text, tbl."time") AS "time",
> max(tbl.pressure) AS pressure
> FROM tbl
> GROUP BY (date_trunc('minute'::text, tbl."time"))
> ORDER BY (date_trunc('minute'::text, tbl."time"));
>
> I then join these tables on the rounded time:
>
> SELECT COALESCE(rain."time", pressures."time", temperatures."time") AS "time",
> rain.rain,
> pressures.pressure,
> temperatures.temperature
> FROM rain
> FULL JOIN pressures USING ("time")
> FULL JOIN temperatures USING ("time");
>
> +------------------------+------+----------+-------------+
> | time | rain | pressure | temperature |
> +------------------------+------+----------+-------------+
> | 2018-09-14 00:00:00+00 | 0 | 1007.52 | 11.349 |
> | 2018-09-14 00:10:00+00 | 0 | 1007.43 | 11.2317 |
> | 2018-09-14 00:20:00+00 | 0 | 1007.28 | 11.2317 |
> +------------------------+------+----------+-------------+
>
> The COALESCE for time and the full joins are needed because some
> columns may be missing for some minutes.
>
> Now I'd like to find the values for a particular short time period:
>
> SELECT * FROM readings
> WHERE "time" BETWEEN '2018-10-01T00:00:00' AND '2018-10-01T24:00:00'

Is readings a table or view?

If view is the SELECT COALESCE ... query the view query?

>
> This works, but it is inefficient; it seems to create all the rounded
> data, do the join on all of it, and then filter on the time period.
> Ideally it would filter the raw data, and then need to round and join
> far fewer rows.
>
> It would not be difficult for me to round the timestamps when inserting
> the data, and also ensure that there is only one row for each minute.
> But I've done some experiments and even if I remove all the rounding and
> replace the full joins with regular joins, it still does sequential
> scans on at least one of the tables:
>
> Nested Loop (cost=12.95..144.99 rows=135 width=20)
> Join Filter: (x_rain."time" = x_pressures."time")
> -> Hash Join (cost=12.67..97.83 rows=135 width=24)
> Hash Cond: (x_temperatures."time" = x_rain."time")
> -> Seq Scan on x_temperatures (cost=0.00..67.50 rows=4350 width=12)
> -> Hash (cost=10.98..10.98 rows=135 width=12)
> -> Index Scan using x_rain_by_time on x_rain (cost=0.28..10.98 rows=135 width=12)
> 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))
> -> Index Scan using x_pressures_by_time on x_pressures (cost=0.28..0.34 rows=1 width=12)
> Index Cond: ("time" = x_temperatures."time")
>
> Maybe that is because the tables are currently relatively small (a
> few thousands rows) and it believes that sequential scans are faster.
> (I have sometimes wished for an "explain" variant that tells me what
> query plan it would choose if all the tables were 100X larger.)
>
> Is there anything I can do to make this more efficient when the tables
> are larger?
>
>
> Thanks for any suggestions.
>
>
> Regards, Phil.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com