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 TABLE IF NOT EXISTS public.activities
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
created_at timestamp with time zone DEFAULT now(),
PRIMARY KEY (id)
);
CREATE INDEX activities_created_at_id ON activities (created_at, id);
INSERT INTO activities (id, created_at) VALUES
('ea7e9cad-89f2-4610-898f-62a04e8b5331', '2024-03-14T07:23:56.474123Z'),
('e34d5557-43d7-4f81-802b-791c213ea5cb', '2024-03-14T07:23:56.474123Z'),
('ded826f7-65f0-4d23-9366-049823ba49ec', '2024-03-14T07:23:56.474222Z'),
('c442f6ea-61bf-42d4-aa85-4eab1d95f569', '2024-03-14T07:23:56.474222Z'),
('bc9763d0-6d24-4fbf-bc23-7eff1589280a', '2024-03-14T07:23:56.474222Z'),
('b9b33ca5-2cd1-490b-a6be-d28784f75b2a', '2024-03-14T07:23:56.474222Z'),
('ac31e591-d4ea-4ef5-956a-c07bd043d9ea', '2024-03-14T07:23:56.474222Z'),
('3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe', '2024-03-14T07:23:56.474222Z'),
('10f1dd9b-2cd8-40bb-a199-2d9c922d07b1', '2024-03-14T07:23:56.474222Z'),
('0cf727a8-5efc-454b-ba1b-ea301e2b1a82', '2024-03-14T07:23:56.474222Z');
-- Just to see the ordering
select id,
to_char(created_at,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') created_at,
to_char(date_trunc('milliseconds', created_at::timestamptz),'YYYY-MM-DD"T"HH24:MI:SS.USTZ') truncated_created_at,
to_char('2024-03-14T07:23:56.474Z'::timestamptz,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target,
to_char(date_trunc('milliseconds','2024-03-14T07:23:56.474Z'::timestamptz),'YYYY-MM-DD"T"HH24:MI:SS.USTZ') truncated_target,
(date_trunc('milliseconds', created_at::timestamptz), id)
> (date_trunc('milliseconds', '2024-03-14T07:23:56.474Z'::timestamptz), 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a') as cond
from activities order by created_at asc, id asc;
CREATE TABLE
CREATE INDEX
INSERT 0 10
| id | created_at | truncated_created_at | target | truncated_target | cond |
|---|---|---|---|---|---|
| e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | t |
| ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | t |
| 0cf727a8-5efc-454b-ba1b-ea301e2b1a82 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | f |
| 10f1dd9b-2cd8-40bb-a199-2d9c922d07b1 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | f |
| 3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | f |
| ac31e591-d4ea-4ef5-956a-c07bd043d9ea | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | f |
| b9b33ca5-2cd1-490b-a6be-d28784f75b2a | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | f |
| bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | t |
| c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | t |
| ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | t |
SELECT 10
-- I want this query to consider the significant digits provided in the date cursor value
select id,
to_char(created_at,'YYYY-MM-DD"T"HH24:MI:SS.USTZ'),
to_char('2024-03-14T07:23:56.474Z'::timestamptz,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target
from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;
| id | to_char | target |
|---|---|---|
| e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC |
| ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC |
| 0cf727a8-5efc-454b-ba1b-ea301e2b1a82 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| 10f1dd9b-2cd8-40bb-a199-2d9c922d07b1 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| 3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| ac31e591-d4ea-4ef5-956a-c07bd043d9ea | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| b9b33ca5-2cd1-490b-a6be-d28784f75b2a | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
| ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC |
SELECT 10
-- What I want, but I do not want to handle this using the query below. The reason is that then I need to be able to get the type of the field and do a conditional query building based on that.
select id,
to_char(created_at,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') created_at,
to_char(date_trunc('milliseconds', created_at::timestamptz),'YYYY-MM-DD"T"HH24:MI:SS.USTZ') truncated_created_at,
to_char('2024-03-14T07:23:56.474Z'::timestamptz,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target,
to_char(date_trunc('milliseconds','2024-03-14T07:23:56.474Z'::timestamptz),'YYYY-MM-DD"T"HH24:MI:SS.USTZ') truncated_target
from activities
where (date_trunc('milliseconds', created_at::timestamptz), id)
> (date_trunc('milliseconds', '2024-03-14T07:23:56.474Z'::timestamptz), 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at desc, id desc;
| id | created_at | truncated_created_at | target | truncated_target |
|---|---|---|---|---|
| ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474222UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474123UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
SELECT 5
--swapping out the column for a generated field
--that drops from PostgreSQL microsecond to javascript millisecond precision
begin;
alter table activities
alter column created_at type timestamptz(3);
--it's working now
select id,
to_char(created_at,'YYYY-MM-DD"T"HH24:MI:SS.USTZ'),
to_char('2024-03-14T07:23:56.474Z'::timestamptz,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target
from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;
rollback;
BEGIN
ALTER TABLE
| id | to_char | target |
|---|---|---|
| bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
SELECT 5
ROLLBACK
--swapping out the column for a generated field
--that drops from PostgreSQL microsecond to javascript millisecond precision
begin;
alter table activities
rename column created_at to old_created_at;
alter table activities
add column created_at timestamptz(3)
generated always as (old_created_at::timestamptz(3)) stored;
--it's working now
select id,
to_char(created_at,'YYYY-MM-DD"T"HH24:MI:SS.USTZ'),
to_char('2024-03-14T07:23:56.474Z'::timestamptz,'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target
from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;
rollback;
BEGIN
ALTER TABLE
ALTER TABLE
| id | to_char | target |
|---|---|---|
| bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
| ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474000UTC | 2024-03-14T07:23:56.474000UTC |
SELECT 5
ROLLBACK