Thread: [MASSMAIL]SupportRequestRows support function for generate_series_timestamptz
[MASSMAIL]SupportRequestRows support function for generate_series_timestamptz
From
David Rowley
Date:
When looking at [1], I noticed that we don't have a prosupport function for the timestamp version of generate_series. We have this for the integer versions of generate_series(), per: postgres=# explain analyze select * from generate_series(1, 256, 2); QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Function Scan on generate_series (cost=0.00..1.28 rows=128 width=4) (actual time=0.142..0.183 rows=128 loops=1) The timestamp version just gives the default 1000 row estimate: postgres=# explain analyze select * from generate_series('2024-01-01', '2025-01-01', interval '1 day'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=8) (actual time=0.604..0.718 rows=367 loops=1) I had some spare time today, so wrote a patch, which gives you: postgres=# explain analyze select * from generate_series('2024-01-01', '2025-01-01', interval '1 day'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Function Scan on generate_series (cost=0.00..3.67 rows=367 width=8) (actual time=0.258..0.291 rows=367 loops=1) This required a bit of hackery to not have timestamp_mi() error out in the planner when the timestamp difference calculation overflows. I considered adding ereturn support to fix that, but that felt like opening Pandora's box. Instead, I added some pre-checks similar to what's in timestamp_mi() to have the support function fall back on the 1000 row estimate when there will be an overflow. Also, there's no interval_div, so the patch has a macro that converts interval to microseconds and does floating point division. I think that's good enough for row estimations. I'll park this here until July CF. (I understand this doesn't help the case in [1] as the generate_series inputs are not const there) David [1] https://www.postgresql.org/message-id/CAMPYKo0FouB-HZ1k-_Ur2v%2BkK71q0T5icQGrp%2BSPbQJGq0H2Rw%40mail.gmail.com