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@sherlock.com.au> sherlock@sherlock.com.au
cid:image010.png@01D16777.DD4FD1E0
Attachments:
image001.jpgimage/jpeg; name=image001.jpgDownload
"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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs