Tom
Snip[ you have not provided sufficient detail to let someone else reproduce
it. ]
Will send a lot more information.. so you can reproduce it.
To get around it.. we have explicit schema referencing and much faster.
Phil McGuinness
-----------
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, 1 July, 2016 12:09 AM
To: Phil McGuinness
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] PostGres 9.5 [ and earlier ] "SET SEARCH_PATH TO "+
cSchema + ";"
"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
