distance

Here's a curious column:

      Materialized view "profiles.venue_parents"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 ...
 distance  | integer |           |          | 

Distance to what? Charing Cross? Linköping Central Station? Document the origin point.

Or better, include the pair of (lat, long)'s, for total of five columns, and then you'll have a self-documenting relation.

I am glad to see it's in SI units of meters.

CREATE MATERIALIZED VIEW venue_parents ...
      ...
      1 AS distance ...

Why a sentinel of one meter, rather than zero?

You seem to sum up +1 and -1 a fair amount -- what does that mean? Maybe you wanted a different word, like priority, to describe your ORDER BY preference, if we don't have actual geographic distances here.

long query

Your WITH root_nodes... query is Too Long. Maybe you can keep all those plates spinning in the air at once, but I can't. Consider breaking out meaningful relations, giving them names, and defining each as a VIEW. Then this long query becomes a much simpler combination of the VIEWs you built up.

Such decomposition doesn't really matter to the backend query planner -- it will catenate them together and still produce plans similar to the current one. The advantage is that you get to do piecewise debugging, of both a result's correctness and also its performance.

Also, the VIEWs would hopefully discourage you from sprinkling expressions like ${filterModel.near?.lng ?? 0} and ${filterModel.near && 1}::int throughout the interior of your queries. Have the query surface attributes like a longitude column, and then let your UI interface select rows WHERE longitude has some value, or perhaps WHERE ST_DistanceSphere() has some value.

meaningful names

    ...
    ) AS t

That's not really helping me out. Give it a proper name, describe the relation you just built.

With p.* you at least told us it's the profiles table, so I know how to mentally pronounce it.

query plan

You didn't mention any symptoms like only getting X result rows per second, or having to wait Y seconds for first result row to appear. Mostly it all looks good to me.

You can use EXPLAIN ANALYZE for more detailed timings. The EXPLAIN will comment on "how many rows?" and "is there an index?", without actually fetching rows. An ANALYZE will fetch the rows, and measure how many seconds that took.

Planning only becomes interesting when you have "lots" of data, and most of it remains on-disk, ignored by a query that looks for just a narrow range of result values. You appear to have only a small amount of data, less than seven hundred rows, which suggests that all queries would be fast, even if there were no indexes. INSERT more rows if you want to explore performance in depth.

The plan, and the use of indexes, mostly looks fine. The only noteworthy item that leaps out at me is this tablescan:

         ->  Seq Scan on venues  (cost=0.00..8144.75 rows=650 width=105)

And of course tablescans are not a bad thing, sometimes that's the very best access plan, if your query needs to retrieve every single stored value.

If we're focused on nice snappy interactive response times, then query selectivity is something you want to pay attention to. A highly selective WHERE clause is one that lets us retrieve just a handful of rows to obtain a result, while we get to ignore 99% of a table's contents. Usually some suitable index will help us achieve that. In contrast a low selectivity query forces the backend to retrieve most of the rows, and there's just no optimizing that, it fundamentally requires doing a lot of time consuming work.

Your query is on the complex side. Think about how storing certain facts in a reporting table would allow you to submit a query that is both simpler for the backend to plan and easier for humans to reason about.

sql92 keywords

I'm not fond of naming a column index, given that that is a pretty important part of DDL. Prefer an alternate spelling, perhaps idx.

paging

Assume that each interactive user has a unique ID, perhaps a GUID in a web session cookie. Equivalently, we could use hash of the first part of a query, omitting the page number.

You run a complex query and then return just the subset of result rows that fall within the requested page. And then re-run it for the subsequent page.

Consider caching the full result set in a reporting table, indexed on guid + row number. Now satisfying a 2nd page or 3rd page request becomes trivial.

Put a timestamp in such rows, so you can purge yesterday's trash.

Depending on user behavior, maybe don't bother with storing results for 1st page requests, if users typically jump around while looking only at 1st page results (so 2nd page effort would be wasted). Once you see a 2nd page request, that may be a fair indicator that user will soon generate 3rd page and 4th page requests, as well.