Re: Slow query performance inside a transaction on a clean database
On Fri, 2021-03-05 at 17:55 +, val.jane...@gmail.com wrote: > I have a SELECT query that uses a long chain of CTEs (6) and is executed > repeatedly as part of the transaction (with different parameters). It is > executed quickly most of the time, but sometimes becomes very slow. I > managed to consistently reproduce the issue by executing a transaction > containing this query on an empty database. The query is fast for the first > 150-170 inserted resources, but ~50% of the executions afterwards take 5.6s > instead of 1.4ms. Additionally it only becomes slow if resources are > inserted in a random order, if I insert resources sorted by > `start_date_time` column the query is always fast. > > Here are the results of EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT > JSON): > - Slow: https://explain.depesz.com/s/e4Fo > - Fast: https://explain.depesz.com/s/7HFJ If your transaction modifies the data significantly (which it does if the table is empty before you start), you should throw in an ANALYZE on the affected tables occasionally. Normally, autovacuum takes care of that, but it cannot see your data until the transaction is committed. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: different execution time for the same query (and same DB status)
You don't mention shared_buffers, which is quite low by default. Not sure of the memory of your docker container, but it might be prudent to increase shared_buffers to keep as much data as possible in memory rather than needing to read from disk by a second run. To test the possibility Tom Lane suggested, do a manual analyze after data insert to see if it is also slow. Explain (analyze, buffers) select... and then using https://explain.depesz.com/ or https://tatiyants.com/pev/#/plans/new would be a good option to have some visualization on where the query is going off the rails.
Users grants with setting options
Hi, I have a quick question, does role custom parameters settings will be granted to users well? Does user c_role will have the same settings m_role.CREATE ROLE m_role ;CREATE ROLE c_role ;ALTER ROLE m_role SET configuration_parameter TO 'VALUE';GRANT m_role TO c_role;
Re: Users grants with setting options
On Mon, Mar 8, 2021 at 4:30 PM Nagaraj Raj wrote: > I have a quick question, does role custom parameters settings will be > granted to users well? > Parameters are not inherited - the role credentials that are logging in are the ones that are used to check for defaults. This "no" is not explicitly documented that I can find; though easy enough to test. David J.
Re: Users grants with setting options
Thank you for confirmation. On Monday, March 8, 2021, 03:46:28 PM PST, David G. Johnston wrote: On Mon, Mar 8, 2021 at 4:30 PM Nagaraj Raj wrote: I have a quick question, does role custom parameters settings will be granted to users well? Parameters are not inherited - the role credentials that are logging in are the ones that are used to check for defaults. This "no" is not explicitly documented that I can find; though easy enough to test. David J.
