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 extension if not exists dblink;
CREATE EXTENSION
create type gender_enum as enum('male','female','other','undisclosed');
create table "User"(
id text primary key default gen_random_uuid()
,"currentChatPartner" text references "User"(id)
,gender gender_enum
,"preferGender" gender_enum
,"lastMatch" text references "User"(id)
,"currentCountry" text
,"ageRange" text
,"createdAt" timestamptz default now() not null
,report int default 0 not null
,online boolean);
insert into "User"
select gen_id as id
,null as "currentChatPartner"
,'undisclosed' as gender
,'undisclosed' as "preferGender"
,null as "lastMatch"
,'CH' as "currentCountry"
,'20-35' as "ageRange"
,now() as "createdAt"
,0 as "report"
,true as "online"
from (values('c63416ba-0031-4f29-b403-ae99f0ba25a6'::uuid)
,('3eb3688b-51ad-4cf3-8c18-1025ed102df7')
,('c24c877f-d1ff-4b93-bb99-cda86403384f')
,('59326fae-2284-4490-858b-57e4fecda2c7') )_(gen_id)
returning *;
CREATE TYPE
CREATE TABLE
| id | currentChatPartner | gender | preferGender | lastMatch | currentCountry | ageRange | createdAt | report | online |
|---|---|---|---|---|---|---|---|---|---|
| c63416ba-0031-4f29-b403-ae99f0ba25a6 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
| 3eb3688b-51ad-4cf3-8c18-1025ed102df7 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
| c24c877f-d1ff-4b93-bb99-cda86403384f | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
| 59326fae-2284-4490-858b-57e4fecda2c7 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
INSERT 0 4
BEGIN TRANSACTION;
SELECT * FROM "User"
WHERE "id" = 'c63416ba-0031-4f29-b403-ae99f0ba25a6'
FOR UPDATE NOWAIT;
SELECT * FROM "User"
WHERE "online" = true
AND "gender" = 'undisclosed'
AND "id" != 'c63416ba-0031-4f29-b403-ae99f0ba25a6'
AND ("lastMatch" != 'c63416ba-0031-4f29-b403-ae99f0ba25a6'
OR "lastMatch" IS NULL)
AND "currentChatPartner" IS NULL
FOR UPDATE
--SKIP LOCKED
LIMIT 1;
--all this is taking place while our transaction remains open
--dblink issues its queries from another, concurrent client
select dblink_connect('another_client','');
select dblink_send_query('another_client',
$s$SELECT "User" FROM "User"
WHERE "online" = true
AND "gender" = 'undisclosed'
AND "id" != 'c24c877f-d1ff-4b93-bb99-cda86403384f'
AND ("lastMatch" != 'c24c877f-d1ff-4b93-bb99-cda86403384f'
OR "lastMatch" IS NULL)
AND "currentChatPartner" IS NULL
FOR UPDATE
--SKIP LOCKED
LIMIT 1;
$s$);
COMMIT TRANSACTION;
select (_.a).* from dblink_get_result('another_client',false)_(a "User");
BEGIN
| id | currentChatPartner | gender | preferGender | lastMatch | currentCountry | ageRange | createdAt | report | online |
|---|---|---|---|---|---|---|---|---|---|
| c63416ba-0031-4f29-b403-ae99f0ba25a6 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
SELECT 1
| id | currentChatPartner | gender | preferGender | lastMatch | currentCountry | ageRange | createdAt | report | online |
|---|---|---|---|---|---|---|---|---|---|
| 3eb3688b-51ad-4cf3-8c18-1025ed102df7 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
SELECT 1
| dblink_connect |
|---|
| OK |
SELECT 1
| dblink_send_query |
|---|
| 1 |
SELECT 1
COMMIT
| id | currentChatPartner | gender | preferGender | lastMatch | currentCountry | ageRange | createdAt | report | online |
|---|---|---|---|---|---|---|---|---|---|
| c63416ba-0031-4f29-b403-ae99f0ba25a6 | null | undisclosed | undisclosed | null | CH | 20-35 | 2024-11-16 11:57:15.867096+00 | 0 | t |
SELECT 1