I am running a query on PostgreSQL+PostGIS+Timescale. The planner, for a subnode, is using a Sequential Scan, so far is reasonable. Nothing too fancy, a filter with a couple of ST_Within and-ed together.
The table is, according to pg_class, 252 relpages big. Butwhen doing an EXPLAIN (ANALYZE, BUFFERS), sometimes the output is giving me a shared_hit_blocks equals to ~400 pages (reasonable, again), but other times more than 2000/3000 blocks!
I have no idea why there is this discrepancy between runs, given that all of them are just a regular seqscan.
edit: adding query and plan:
SELECT *
FROM
public.a1 AS ST
JOIN public.a2 as PS ON ST.id = PS.id
WHERE
(
st_dwithin(
ST.geom :: geography,
ST_GeomFromText(
'POINT(144.99462710977556 -37.86733663123632)',
4326
) :: geography,
500.0
)
AND ts BETWEEN '2021-11-04 20:45:00'
AND '2021-11-04 21:15:00'
)
OR (
st_dwithin(
ST.geom :: geography,
ST_GeomFromText(
'POINT(144.96670586140522 -37.82335536330383)',
4326
) :: geography,
500.0
)
AND ts BETWEEN '2021-11-04 18:45:00'
AND '2021-11-04 19:15:00'
)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=13707.37..198740.03 rows=2375 width=20) (actual time=49.509..90.131 rows=2149 loops=1)
Workers Planned: 5
Workers Launched: 5
Buffers: shared hit=4995
-> Hash Join (cost=12707.37..197502.53 rows=475 width=20) (actual time=43.567..51.215 rows=358 loops=6)
Hash Cond: (ps.streetid = st.id)
Join Filter: ((((st.geom)::geography && '0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography) AND ('0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography && _st_expand((st.geom)::geography, '500'::double precision)) AND _st_dwithin((st.geom)::geography, '0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography, '500'::double precision, true) AND (ps.ts >= '2021-11-04 20:45:00'::timestamp without time zone) AND (ps.ts <= '2021-11-04 21:15:00'::timestamp without time zone)) OR (((st.geom)::geography && '0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography) AND ('0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography && _st_expand((st.geom)::geography, '500'::double precision)) AND _st_dwithin((st.geom)::geography, '0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography, '500'::double precision, true) AND (ps.ts >= '2021-11-04 18:45:00'::timestamp without time zone) AND (ps.ts <= '2021-11-04 19:15:00'::timestamp without time zone)))
Rows Removed by Join Filter: 358
Buffers: shared hit=4995
-> Parallel Bitmap Heap Scan on _hyper_1_6_chunk ps (cost=2376.43..187078.33 rows=35527 width=20) (actual time=0.958..2.776 rows=29946 loops=6)
Recheck Cond: (((ts >= '2021-11-04 20:45:00'::timestamp without time zone) AND (ts <= '2021-11-04 21:15:00'::timestamp without time zone)) OR ((ts >= '2021-11-04 18:45:00'::timestamp without time zone) AND (ts <= '2021-11-04 19:15:00'::timestamp without time zone)))
Heap Blocks: exact=897
Buffers: shared hit=1616
-> BitmapOr (cost=2376.43..2376.43 rows=177939 width=0) (actual time=5.489..5.490 rows=0 loops=1)
Buffers: shared hit=468
-> Bitmap Index Scan on _hyper_1_6_chunk_a2_ts_idx (cost=0.00..1135.80 rows=88334 width=0) (actual time=2.832..2.832 rows=89838 loops=1)
Index Cond: ((ts >= '2021-11-04 20:45:00'::timestamp without time zone) AND (ts <= '2021-11-04 21:15:00'::timestamp without time zone))
Buffers: shared hit=234
-> Bitmap Index Scan on _hyper_1_6_chunk_a2_ts_idx (cost=0.00..1151.81 rows=89605 width=0) (actual time=2.655..2.655 rows=89838 loops=1)
Index Cond: ((ts >= '2021-11-04 18:45:00'::timestamp without time zone) AND (ts <= '2021-11-04 19:15:00'::timestamp without time zone))
Buffers: shared hit=234
-> Hash (cost=10326.69..10326.69 rows=340 width=94) (actual time=42.381..42.381 rows=307 loops=6)
Buckets: 1024 Batches: 1 Memory Usage: 47kB
Buffers: shared hit=3192
-> Seq Scan on a1 st (cost=0.00..10326.69 rows=340 width=94) (actual time=16.930..42.289 rows=307 loops=6)
Filter: ((((geom)::geography && '0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography) AND ('0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography && _st_expand((geom)::geography, '500'::double precision)) AND _st_dwithin((geom)::geography, '0101000020E61000006F863BFCD31F624033E400E304EF42C0'::geography, '500'::double precision, true)) OR (((geom)::geography && '0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography) AND ('0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography && _st_expand((geom)::geography, '500'::double precision)) AND _st_dwithin((geom)::geography, '0101000020E6100000C8722141EF1E6240263063B563E942C0'::geography, '500'::double precision, true)))
Rows Removed by Filter: 12527
Buffers: shared hit=3192
Planning Time: 1.867 ms
JIT:
Functions: 102
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 17.370 ms, Inlining 0.000 ms, Optimization 4.312 ms, Emission 90.685 ms, Total 112.367 ms
Execution Time: 139.958 ms
(34 rows)
