"Phil McGuinness" <sherlock@sherlock.com.au> writes:
> 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.
This is really hard to believe, and you have not provided sufficient
detail to let someone else reproduce it.
A possible guess, though, comes from the fact that the default value of
search_path is *not* empty; it is
# show search_path ;
search_path
-----------------
"$user", public
(1 row)
If you are doing exactly what you show above, then you removed "public"
from your search path, as well as the schema corresponding to your user
name (if there is one). I wonder whether the "sherdata" schema contains
a different and slower version of some function or view than exists in
"public".
regards, tom lane