Thoughts?
I have a feeling this is over-engineering in slightly different direction, solving the way for hack to work instead of original problem.
What's currently happening in PostGIS is that there are functions that need to perform index-based lookups.
Postgres is unable to plan this for functions, only for operators.
Operators have only two sides, some PostGIS functions have arguments - you can't squeeze these into operator.
Well, you can squeeze two of your parameters into one, but it will be ugly too - you'll invent some "query" argument type and alternative grammar instead of SQL (see tsquery).
ST_DWithin itself is also another way of working around planner limitation and squeezing something into both sides of operator, since you don't know which side of your query is going to have an index. It's not perfect either.
A perfect solution will be a way to perform a clean index scan on ST_Distance(a.geom, b.geom) < 10, which is what ST_DWithin is trying to express in limited logic of "you only have two sides of operator".
If you need example from another world: imagine jsonb key-value lookup. It's currently done via
select ... where tags @> '{"highway":"residential"}';
- which is hard: you have to remember which side the rose should lean towards, which {} [] to use, how to quote around json and inside and more.
A more intuitive way for many programmers to write this is similar to this:
select ... where (tags->>'highway') = 'residential';
- but this does not end up with an index lookup.
I'd be happy if we can deprecate ST_DWithin in PostGIS and just allow ST_Distance(a.geom, b.geom) < 10.
ST_Distance is defined in standard as function, however, there is equivalent operator <-> that exists for sole purpose of KNN lookups. So, when you write:
... order by ST_Distance(geom, 'your_position')
- you're not getting index scan, and when writing
... order by geom <-> 'your_position'
- you're getting index scan but not doing a thing you may intuitively write by knowing ST_Distance is standard-defined way to measure distance between two spatial objects.
May it happen to direct you to some other thoughts?
