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 FUNCTION f_woy(timestamp with time zone)
RETURNS int AS $$
SELECT CASE WHEN (date_part('year',$1)::int % 4 = 0) AND ((date_part('year',$1)::int % 100 <> 0) OR (date_part('year',$1)::int % 400 = 0))
THEN to_char($1, 'WW')::int -1
ELSE to_char($1, 'WW')::int
END
$$ LANGUAGE sql IMMUTABLE STRICT;
SELECT f_woy(('2007-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at
time zone 'america/los_angeles')::date), f_woy(('2008-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at
time zone 'america/los_angeles')::date)
| f_woy | f_woy |
|---|---|
| 25 | 25 |
--------------------------------------------------------------------------------
-- ① 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
1048576 rows affected