# distance

Here's a curious column:
```
      Materialized view "profiles.venue_parents"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 ...
 distance  | integer |           |          | 
```
Distance to what?
[Charing Cross](https://en.wikipedia.org/wiki/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.

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](https://en.wikipedia.org/wiki/Query_optimization#Cost_estimation)
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](https://en.wikipedia.org/wiki/Data_definition_language).
Prefer an alternate spelling, perhaps `idx`.

----

# paging

Assume that each interactive user has a unique ID,
perhaps a GUID in a web session cookie.

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.