SupportRequestRows support function for generate_series_timestamptz

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: SupportRequestRows support function for generate_series_timestamptz
Date: 2024-04-14 03:14:46
Message-ID: CAApHDvrBE=+ASo_sGYmQJ3GvO8GPvX5yxXhRS=t_ybd4odFkhQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-support-function-for-generate_series-for-time.patch text/plain 5.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2024-04-14 03:16:56 Re: Cleaning up threading code
Previous Message Dmitry Koterov 2024-04-14 02:05:59 Re: In MacOS, psql reacts on SIGINT in a strange fashion (Linux is fine)