We need a function to retrieve a set of properties based on either a neighborhood name or a PostGIS geometric filter.
The neighborhood matching is much faster, so when possible we use that, with the geo filter as fallback.
The original function that matched only on neighborhood was very fast:
func_a:
CREATE OR REPLACE FUNCTION func_a( _neighborhood text )
RETURNS TABLE(f1 int) AS $$
SELECT id FROM parcels
WHERE neighborhood = _neighborhood
ORDER BY id
LIMIT 10 $$
LANGUAGE SQL;
I then added the alternative geo parameter (passed as WKT geo text) and followed the method demonstrated here and here to effectively ignore whichever parameter was NULL:
func_b:
CREATE OR REPLACE FUNCTION func_b( _neighborhood text = NULL
, _geo text = NULL)
RETURNS TABLE(f1 int) AS $$
SELECT id FROM parcels
WHERE (_neighborhood IS NULL OR neighborhood = _neighborhood)
AND (_geo IS NULL OR ST_Intersects(geometry, ST_GeomFromText(_geo, 3857)))
ORDER BY id
LIMIT 10 $$
LANGUAGE SQL;
And while func_b worked, it was much slower than func_a, even when only the _neighborhood param was passed. In fact func_b wound up being just as slow whether _neighborhood or _geo param was passed, which doesn't make sense to me. If func_b is called with using a valid _neighborhood param and a NULL _geo param, shouldn't it perform the same as func_a?
