upstream/downstream functions on pg17

[smnorris]

Running the test still gives below errors, and the indexes are not being used - the final query in the test sql is v slow.

psql $DATABASE_URL -f test_FWA_Upstream.sql
psql:test_FWA_Upstream.sql:9: ERROR:  SET is not allowed in a non-volatile function
CONTEXT:  SQL function "fwa_upstream" during startup

[smnorris]

pg16

bcfishpass=# explain analyze SELECT
  ROUND((SUM(ST_Length(geom)) / 1000)::numeric, 2) as index_check_is_this_fast
FROM whse_basemapping.fwa_stream_networks_sp a
WHERE FWA_Upstream(354153927, 10, '930.023810'::ltree, '930.023810'::ltree, a.blue_line_key, a.downstream_route_measure, a.wscode_ltree, a.localcode_ltree);
;
                                                                                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=555.04..555.05 rows=1 width=32) (actual time=258.167..258.169 rows=1 loops=1)
   ->  Index Scan using fwa_streamnetworks_wsc_gist_idx on fwa_stream_networks_sp a  (cost=0.41..526.48 rows=224 width=932) (actual time=2.922..256.187 rows=1504 loops=1)
         Index Cond: (wscode_ltree <@ '930.023810'::ltree)
         Filter: CASE WHEN (((blue_line_key <> 354153927) OR ((blue_line_key = 354153927) AND (downstream_route_measure >= '10.001'::double precision))) AND (('930.023810'::ltree <> wscode_ltree) OR ('930.023810'::ltree <> localcode_ltree) OR (354153927 = blue_line_key))) THEN true ELSE false END
         Rows Removed by Filter: 7
 Planning Time: 1.986 ms
 Execution Time: 263.627 ms

pg17

bcfishpass_test=# explain analyze SELECT
  ROUND((SUM(ST_Length(geom)) / 1000)::numeric, 2) as index_check_is_this_fast
FROM whse_basemapping.fwa_stream_networks_sp a
WHERE FWA_Upstream(354153927, 10, '930.023810'::ltree, '930.023810'::ltree, a.blue_line_key, a.downstream_route_measure, a.wscode_ltree, a.localcode_ltree);
                                                                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=45342.04..45342.06 rows=1 width=32) (actual time=4349.021..4352.637 rows=1 loops=1)
   ->  Gather  (cost=45341.62..45342.03 rows=4 width=8) (actual time=4347.341..4352.612 rows=5 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate  (cost=44341.62..44341.63 rows=1 width=8) (actual time=4332.782..4332.783 rows=1 loops=5)
               ->  Parallel Seq Scan on fwa_stream_networks_sp a  (cost=0.00..42578.43 rows=13829 width=1043) (actual time=3309.797..4332.561 rows=301 loops=5)
                     Filter: fwa_upstream(354153927, '10'::double precision, '930.023810'::ltree, '930.023810'::ltree, blue_line_key, downstream_route_measure, wscode_ltree, localcode_ltree, false, '0.001'::double precision)
                     Rows Removed by Filter: 32888
 Planning Time: 1.506 ms
 Execution Time: 4369.574 ms
(10 rows)

[smnorris]

This is related to #163 - remove the new set search_path statement and the function returns to using the index.

[smnorris]

Adding the newly required SET prevents the functions from being inlined: https://dba.stackexchange.com/questions/343764/pg17-functions-used-in-materialized-views-not-using-index-when-search-path-is-se

Stick to v16 for now. When an upgrade is required, work around by either:

• spelling out the full upstream/downstream query in the materialized view sql rather than using functions
• retain the functions, but do not create materialized views, create derivative tables instead