Postgres 13

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 bulk_sample (
    serial_number varchar(255),  -- odd type decision?
    validation_date timestamp,
    station_id integer,
    direction integer
);

INSERT INTO bulk_sample VALUES
  ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:31:58', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:50:22', 113, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:16:56', 113, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:47:06', 120, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:02:12', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:47:45', 102, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 19:26:38', 102, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 20:17:24', 120, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 07:58:20', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 08:43:35', 104, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 16:38:10', 104, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:15:01', 119, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:42:29', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:48:05', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:17:59', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:25:25', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:16:12', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:32:51', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:31:20', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:39:33', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 20:57:50', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 21:16:25', 120, 2);
22 rows affected

-- simple solution if transactions never overlap for the same serial_number
WITH cte AS (
   SELECT serial_number, validation_date, station_id, direction
        , row_number() OVER (PARTITION BY serial_number ORDER BY validation_date) AS rn
   FROM   bulk_sample
   WHERE  validation_date >= '2020-02-01'
   AND    validation_date <  '2020-02-02'  -- entry & exit must be within time frame
   )
SELECT s.station_id AS source, d.station_id AS dest, count(*)
FROM   cte s
JOIN   cte d USING (serial_number)
WHERE  s.direction = 1
AND    d.rn = s.rn + 1
GROUP  BY 1, 2
ORDER  BY 1, 2;  -- optional sort order


source
dest
count


102
120
1

104
119
1

113
120
1

118
120
2

119
104
1

119
120
1

120
102
1

120
113
1

120
118
2


-- consider 2x UUID:
SELECT *
     , replace(uuid1::text || uuid2::text, '-', '') AS reverse_engineered
     , replace(uuid1::text || uuid2::text, '-', '') = serial_number AS identical
     , pg_column_size(serial_number) AS varchar_size
     , pg_column_size(uuid1) + pg_column_size(uuid2) AS uuid_size
FROM  (
   SELECT serial_number
        , left(serial_number, 32)::uuid  AS uuid1
        , right(serial_number, 32)::uuid AS uuid2
   FROM   bulk_sample
   ) sub;
   


serial_number
uuid1
uuid2
reverse_engineered
identical
varchar_size
uuid_size


019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
019b5526-970f-cfcf-7813-e9fe1acf8a41
bcaf5a5a-5c10-870b-3211-d82f63fbf270
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
23cc9678-e8cf-834d-ecb0-96ba36be0efe
e418402b-ce03-aab5-2e69-026adfec7663
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
2a8f28bf-0afc-6552-10aa-337aff016d33
100282ac-73cc-a660-a397-b924808499af
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af
t
65
32

source	dest	count
102	120	1
104	119	1
113	120	1
118	120	2
119	104	1
119	120	1
120	102	1
120	113	1
120	118	2

serial_number	uuid1	uuid2	reverse_engineered	identical	varchar_size	uuid_size
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	019b5526-970f-cfcf-7813-e9fe1acf8a41	bcaf5a5a-5c10-870b-3211-d82f63fbf270	019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	23cc9678-e8cf-834d-ecb0-96ba36be0efe	e418402b-ce03-aab5-2e69-026adfec7663	23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	2a8f28bf-0afc-6552-10aa-337aff016d33	100282ac-73cc-a660-a397-b924808499af	2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af	t	65	32

source	dest	count
102	120	1
104	119	1
113	120	1
118	120	2
119	104	1
119	120	1
120	102	1
120	113	1
120	118	2

source	dest	count
102	120	1
104	119	1
113	120	1
118	120	2
119	104	1
119	120	1
120	102	1
120	113	1
120	118	2