tstzrange on large table gives poor estimate of expected rows - Mailing list pgsql-general

Hi,

We have a fairly big table (22 million rows) which has a start and end
timestamp with time zone and other columns.  Some of the columns plus
start timestamp make a primary key.  The end timestamp is exactly one
day ahead of the start timestamp for each row and there are
approximately 10000 rows per day, so each day there will be about
10000 inserts (and many updates) where the start is yyyy-mm-dd
00:00:00.0 and the end column is one day ahead of that.  We have
created a tstzrange on start and end column and then do a query which
looks for exactly one day range:

explain (analyze, buffers) select tstzrange_test.interval_start_date
as interval_start_date
from tstzrange_test tstzrange_test
where tstzrange('2021-07-20 00:00:00.0', '2021-07-21 00:00:00.0',
'[]') @> tstzrange(tstzrange_test.interval_start_date,
tstzrange_test.interval_end_date, '[]');

In the real query this result is then grouped other columns then
joined on another table which has similar results but by the hour
instead of by the day.

The query always underestimates the number of rows:

Index Scan using tstzrange_test_ix01 on tstzrange_test
(cost=0.41..8.43 rows=1 width=8) (actual time=0.347..8.889 rows=10000
loops=1)
   Index Cond: (tstzrange(interval_start_date, interval_end_date,
'[]'::text) <@ '["2021-07-20 00:00:00+00","2021-07-21
00:00:00+00"]'::tstzrange)
   Buffers: shared hit=815
 Planning Time: 0.120 ms
 Execution Time: 9.591 ms
(5 rows)

and when this is aggregated and then joined on another table it leads
to a slow query.  If I change the query so the start date is 12 hours
earlier (ie so no extra results would be found as the previous day
start date would be 24 hours earlier) it gives a better estimate and
uses a different plan:

explain (analyze, buffers) select tstzrange_test.interval_start_date
as interval_start_date
from tstzrange_test tstzrange_test
where tstzrange('2021-07-19 12:00:00.0', '2021-07-21 00:00:00.0',
'[]') @> tstzrange(tstzrange_test.interval_start_date,
tstzrange_test.interval_end_date, '[]');

 Bitmap Heap Scan on tstzrange_test  (cost=199.89..11672.03 rows=4577
width=8) (actual time=6.880..7.844 rows=10000 loops=1)
   Recheck Cond: ('["2021-07-19 12:00:00+00","2021-07-21
00:00:00+00"]'::tstzrange @> tstzrange(interval_start_date,
interval_end_date, '[]'::text))
   Heap Blocks: exact=65
   Buffers: shared hit=330
   ->  Bitmap Index Scan on tstzrange_test_ix01  (cost=0.00..198.74
rows=4577 width=0) (actual time=6.866..6.866 rows=10000 loops=1)
         Index Cond: (tstzrange(interval_start_date,
interval_end_date, '[]'::text) <@ '["2021-07-19
12:00:00+00","2021-07-21 00:00:00+00"]'::tstzrange)
         Buffers: shared hit=265
 Planning Time: 0.157 ms
 Execution Time: 8.186 ms
(9 rows)

I have tried increasing the states to 10000 on each of the timestamp
columns and also setting up a statistics object to say that start and
end are related (dependencies).

This is how I created and  populated the table:

CREATE TABLE tstzrange_test (
    interval_start_date        timestamp with time zone,
    interval_end_date timestamp with time zone,
    user_registration_id bigint
);

insert into tstzrange_test (interval_start_date, interval_end_date,
user_registration_id) select '2021-01-01 00:00:00.0'::timestamp with
time zone + ((psid)/10000 || ' day')::interval, '2021-01-02
00:00:00.0'::timestamp with time zone + ((psid)/10000 || '
day')::interval, floor(random() * (500) + 1)::int from
generate_series(1,3600000) as s(psid);

CREATE INDEX tstzrange_test_ix01 ON tstzrange_test USING gist
(tstzrange(interval_start_date, interval_end_date, '[]'::text));


Any help on how I can make the planner estimate better would be much
appreciated.

Thanks.