By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create or replace function IsLeapYear(int)
returns boolean as $$
select $1 % 4 = 0 and ($1 % 100 <> 0 or $1 % 400 = 0)
$$ LANGUAGE sql IMMUTABLE STRICT;
create or replace function f_woy(date)
returns int language plpgsql as $$
declare
currentYear int = extract (year from $1);
LeapYearShift int = 1 + (IsLeapYear(currentYear) and $1 > make_date(currentYear, 2, 28))::int;
begin
return ((extract(doy from $1)::int)- LeapYearShift) / 7+ 1;
end;
$$;
CREATE TABLE T(normalDt timestamp,LeapYearDt timestamp);
INSERT INTO T VALUES ('2007-01-05 14:19:46.502-07'::timestamp,'2008-01-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-02-05 14:19:46.502-07'::timestamp,'2008-02-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-03-05 14:19:46.502-07'::timestamp,'2008-03-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-04-05 14:19:46.502-07'::timestamp,'2008-04-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-05-05 14:19:46.502-07'::timestamp,'2008-05-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-06-05 14:19:46.502-07'::timestamp,'2008-06-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-07-05 14:19:46.502-07'::timestamp,'2008-07-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-08-05 14:19:46.502-07'::timestamp,'2008-08-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-09-05 14:19:46.502-07'::timestamp,'2008-09-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-10-05 14:19:46.502-07'::timestamp,'2008-10-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-11-05 14:19:46.502-07'::timestamp,'2008-11-05 14:19:46.502-07'::timestamp);
INSERT INTO T VALUES ('2007-12-05 14:19:46.502-07'::timestamp,'2008-12-05 14:19:46.502-07'::timestamp);
INSERT 0 1
SELECT
f_woy((normalDt::timestamp at time zone 'UTC' at
time zone 'america/los_angeles')::date),
f_woy((LeapYearDt::timestamp at time zone 'UTC' at
time zone 'america/los_angeles')::date)
FROM T
| f_woy | f_woy |
|---|---|
| 1 | 1 |
| 6 | 6 |
| 10 | 10 |
| 14 | 14 |
| 18 | 18 |
| 23 | 23 |
| 27 | 27 |
| 31 | 31 |
| 36 | 36 |
| 40 | 40 |
| 45 | 45 |
| 49 | 49 |
SELECT 12
--------------------------------------------------------------------------------
-- ① Execution Plans
--------------------------------------------------------------------------------
set statistics profile on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics profile off;
ERROR: syntax error at or near "profile" LINE 4: set statistics profile on; ^
--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics xml off;
ERROR: syntax error at or near "xml" LINE 5: set statistics xml on; ^
--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
, p2(i) as (select 1 from p1 as a, p1 as b)--1M rows
select row_number() over(order by i) as val
into integers
from p2
SELECT 1048576