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
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Add-support-function-for-generate_series-for-time.patch | text/plain | 5.0 KB |
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) |