I have an API where users can retrieve a list of "venues".
- Every venue is a profile, but not every profile is a venue
- Venues can be "parents" of other venues (think hotel - floor - room)
- When a latitude and longitude is given, the list should be sorted by distance
- When a latitude and longitude is not given, the list should be sorted alphabetically, by venue name (located in the
profiles.profilestable) - Clients use key-based pagination (i.e. a fixed number of results is returned per page. to get the next page, the client should include the first id of the next page - hence
LIMIT env.RESULT_PAGE_SIZE + 1).
I have constructed a query, utilizing multiple CTEs and "short circuits" (false AND condition). However, I'm still quite new to CTEs and would appreciate some feedback on the performance of my query. I have also attached the query plan and would appreciate a step-by-step explanation of how to read it and what conclusions to draw.
Context
dansdata=# \d profiles.profiles
Table "profiles.profiles"
Column | Type | Collation | Nullable | Default
-----------------+--------------------------+-----------+----------+---------
id | text | | not null |
type | profiles.profile_type | | not null |
name | text | | not null |
description | text | | not null |
created_at | timestamp with time zone | | not null | now()
cover_image_id | text | | |
poster_image_id | text | | |
square_image_id | text | | |
Indexes:
"profiles_pkey" PRIMARY KEY, btree (id)
"profiles_cover_image_id_idx" btree (cover_image_id)
"profiles_name_idx" gin (name gin_trgm_ops)
"profiles_poster_image_id_idx" btree (poster_image_id)
"profiles_square_image_id_idx" btree (square_image_id)
Foreign-key constraints:
"profiles_cover_image_id_fkey" FOREIGN KEY (cover_image_id) REFERENCES storage.images(id) ON UPDATE CASCADE ON DELETE SET NULL
"profiles_poster_image_id_fkey" FOREIGN KEY (poster_image_id) REFERENCES storage.images(id) ON UPDATE CASCADE ON DELETE SET NULL
"profiles_square_image_id_fkey" FOREIGN KEY (square_image_id) REFERENCES storage.images(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "events.event_slot_participants" CONSTRAINT "event_slot_participants_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "profiles.individuals" CONSTRAINT "individuals_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "profiles.organizations" CONSTRAINT "organizations_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "profiles.profile_links" CONSTRAINT "profile_links_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "profiles.venues" CONSTRAINT "venues_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE CASCADE
dansdata=# \d profiles.venues
Table "profiles.venues"
Column | Type | Collation | Nullable | Default
--------------------+-----------------------+-----------+----------+---------
profile_id | text | | not null |
parent_id | text | | |
coords | geography(Point,4326) | | not null |
permanently_closed | boolean | | not null | false
Indexes:
"venues_pkey" PRIMARY KEY, btree (profile_id)
"venues_coords_idx" gist (coords)
Foreign-key constraints:
"venues_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES profiles.venues(profile_id) ON UPDATE CASCADE ON DELETE SET NULL
"venues_profile_id_fkey" FOREIGN KEY (profile_id) REFERENCES profiles.profiles(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "events.event_slots" CONSTRAINT "event_slots_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES profiles.venues(profile_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "profiles.venues" CONSTRAINT "venues_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES profiles.venues(profile_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
enforce_non_circular_hierarchy_insert BEFORE INSERT ON profiles.venues FOR EACH ROW WHEN (new.parent_id IS NOT NULL) EXECUTE FUNCTION profiles.trigger_func_venue_enforce_non_circular_hierarchy()
enforce_non_circular_hierarchy_update BEFORE UPDATE ON profiles.venues FOR EACH ROW WHEN (new.parent_id IS NOT NULL AND (old.parent_id IS DISTINCT FROM new.parent_id OR new.profile_id IS DISTINCT FROM old.profile_id)) EXECUTE FUNCTION profiles.trigger_func_venue_enforce_non_circular_hierarchy()
refresh_venue_parents AFTER INSERT OR DELETE OR UPDATE OF parent_id OR TRUNCATE ON profiles.venues FOR EACH STATEMENT EXECUTE FUNCTION profiles.trigger_func_refresh_venue_parents()
dansdata=# \d profiles.venue_parents
Materialized view "profiles.venue_parents"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
child_id | text | | |
parent_id | text | | |
distance | integer | | |
Indexes:
"venue_distance_idx" btree (distance)
"venue_parents_child_idx" btree (child_id)
"venue_parents_child_parent_idx" UNIQUE, btree (child_id, parent_id)
"venue_parents_parent_idx" btree (parent_id)
CREATE MATERIALIZED VIEW "profiles"."venue_parents"(child_id, parent_id, distance) AS (
WITH RECURSIVE parent_query AS (
SELECT
profile_id AS child_id,
parent_id,
1 AS distance
FROM profiles.venues
WHERE parent_id IS NOT NULL
UNION ALL
SELECT
parent_query.child_id AS child_id,
profiles.venues.parent_id AS parent_id,
parent_query.distance + 1 AS distance
FROM parent_query, profiles.venues
WHERE profiles.venues.parent_id IS NOT NULL
AND profiles.venues.profile_id = parent_query.parent_id
)
SELECT child_id, parent_id, distance
FROM parent_query
);
CREATE UNIQUE INDEX "venue_parents_child_parent_idx" ON "profiles"."venue_parents"(child_id, parent_id);
CREATE INDEX "venue_parents_child_idx" ON "profiles"."venue_parents"(child_id);
CREATE INDEX "venue_parents_parent_idx" ON "profiles"."venue_parents"(parent_id);
CREATE INDEX "venue_distance_idx" ON "profiles"."venue_parents"(distance);
Query Under Review
WITH
root_nodes(profile_id) AS (
SELECT profile_id
FROM profiles.venues
WHERE profile_id NOT IN (
SELECT child_id
FROM profiles.venue_parents
)
),
leaf_nodes(profile_id) AS (
SELECT profile_id
FROM profiles.venues
WHERE profile_id NOT IN (
SELECT parent_id
FROM profiles.venue_parents
)
),
joined AS (
SELECT
p.*,
t.*
FROM (
(
SELECT
*,
-1 AS distance
FROM profiles.venues
WHERE ${filterModel.near && 1}::int IS NULL
)
UNION ALL
(
SELECT
*,
ST_DistanceSphere(
coords::geometry,
ST_MakePoint(${filterModel.near?.lng ?? 0}, ${filterModel.near?.lat ?? 0})
) AS distance
FROM profiles.venues
WHERE ${filterModel.near && 1}::int IS NOT NULL
)
) AS t
INNER JOIN profiles.profiles p
ON p.id = t.profile_id
),
sorted AS (
SELECT
*
FROM joined
ORDER BY
distance ASC,
name ASC,
id ASC
),
enumerated AS (
SELECT
*,
ROW_NUMBER() OVER () AS index
FROM sorted
)
SELECT id AS "profileId"
FROM enumerated
WHERE (
${filterModel.includePermanentlyClosed}
OR permanently_closed = FALSE
) AND (
${filterModel.level === "any"}
OR (
${filterModel.level === "root"}
AND profile_id IN (SELECT * FROM root_nodes)
)
OR (
${filterModel.level === "leaf"}
AND profile_id IN (SELECT * FROM leaf_nodes)
)
) AND (
index >= COALESCE(
(
SELECT index
FROM enumerated
WHERE id = ${filterModel.pageKey}
),
0
)
)
ORDER BY index
LIMIT ${env.RESULT_PAGE_SIZE + 1}
Example with actual values
┌──────────────────────────┬───────────────────┬────────────────────┬────────┐
│ (index) │ lat │ lng │ Values │
├──────────────────────────┼───────────────────┼────────────────────┼────────┤
│ env.RESULT_PAGE_SIZE. │ │ │ 30 │
│ level │ │ │ 'any' │
│ includePermanentlyClosed │ │ │ true │
│ near │ 58.41616195587502 │ 15.625933242341707 │ │
└──────────────────────────┴───────────────────┴────────────────────┴────────┘
WITH
root_nodes(profile_id) AS (
SELECT profile_id
FROM profiles.venues
WHERE profile_id NOT IN (
SELECT child_id
FROM profiles.venue_parents
)
),
leaf_nodes(profile_id) AS (
SELECT profile_id
FROM profiles.venues
WHERE profile_id NOT IN (
SELECT parent_id
FROM profiles.venue_parents
)
),
joined AS (
SELECT
p.*,
t.*
FROM (
(
SELECT
*,
-1 AS distance
FROM profiles.venues
WHERE 1::int IS NULL
)
UNION ALL
(
SELECT
*,
ST_DistanceSphere(
coords::geometry,
ST_MakePoint(15.625933242341707, 58.41616195587502)
) AS distance
FROM profiles.venues
WHERE 1::int IS NOT NULL
)
) AS t
INNER JOIN profiles.profiles p
ON p.id = t.profile_id
),
sorted AS (
SELECT
*
FROM joined
ORDER BY
distance ASC,
name ASC,
id ASC
),
enumerated AS (
SELECT
*,
ROW_NUMBER() OVER () AS index
FROM sorted
)
SELECT id AS "profileId"
FROM enumerated
WHERE (
true
OR permanently_closed = FALSE
) AND (
true
OR (
false
AND profile_id IN (SELECT * FROM root_nodes)
)
OR (
false
AND profile_id IN (SELECT * FROM leaf_nodes)
)
) AND (
index >= COALESCE(
(
SELECT index
FROM enumerated
WHERE id = null
),
0
)
)
ORDER BY index
LIMIT 31
Query Plan
Output from EXPLAIN ..., given the above example with actual values and run on a database instance with only 1 profile, which is not a venue.
QUERY PLAN
---------------------------------------------------------------------------------------------------
Limit (cost=8248.43..8248.51 rows=31 width=40)
CTE enumerated
-> WindowAgg (cost=8211.05..8227.32 rows=651 width=317)
-> Sort (cost=8211.05..8212.67 rows=651 width=309)
Sort Key: (('-1'::integer)::double precision), p.name, p.id
-> Hash Join (cost=17.88..8180.62 rows=651 width=309)
Hash Cond: ("*SELECT* 1".profile_id = p.id)
-> Append (cost=0.00..8154.51 rows=651 width=105)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..0.00 rows=1 width=105)
-> Result (cost=0.00..0.00 rows=0 width=101)
One-Time Filter: false
-> Seq Scan on venues (cost=0.00..8144.75 rows=650 width=105)
-> Hash (cost=13.50..13.50 rows=350 width=204)
-> Seq Scan on profiles p (cost=0.00..13.50 rows=350 width=204)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
-> Sort (cost=21.11..21.65 rows=217 width=40)
Sort Key: enumerated.index
-> CTE Scan on enumerated (cost=0.00..14.65 rows=217 width=40)
Filter: (index >= COALESCE($1, '0'::bigint))
(21 rows)
