PostGres 9.5 [ and earlier ] "SET SEARCH_PATH TO "+ cSchema + ";"

Hi,

Enjoy Postgres very much and have been in a long journey of several years to
convert and older Windows system using DBF/FPT to all SQL.

We had a directory structure in old system to connect to history for each
year..

We SCHEMA's to represent that directory structure.

So we have database with license number say 1234567-7654321 and schemas ;
say SHER2016 and SHER 2015 back to say SHER1997

In each schema there is basically the same 49 databases [ tables, indexes
etc ]

We used "SET SEARCH_PATH TO "+ cSchema + ";" to set say SHER2016 or
SHERDATA.. where ever we need to be and open tables as normal.

"INSERT INTO settings SELECT * FROM settings;") // We can use this
syntax rather explicit say sherdata.

.. Verse ..

"INSERT INTO settings SELECT * FROM sherdata.settings;")

When we use code with SET SEARCH PATH and not explicit schema the speed
difference is 8 time slower.

We are seeing 331 milliseconds down to 30 milliseconds by not using
SEARCH_PATH

We have spent days changing our syntax to pass the schema name [ path ]
through our the code.

Would have believe the SET SEARCH_PATH would have worked faster or the
same.. but it is not.

Thought you should know.

Phil McGuinness - General Manager - Sherlock Software
<mailto:sherlock(at)sherlock(dot)com(dot)au> sherlock(at)sherlock(dot)com(dot)au

cid:image010(dot)png(at)01D16777(dot)DD4FD1E0