On 11/21/2014 06:54 AM, Nestor A. Diaz wrote:
On 11/20/2014 12:18 PM, Adrian Klaver wrote:
What query?
How is it executed?
Hi Adrian, this is one of the queries that appear to consume all
resources, we use a CTE approach ("with") because in 9.1 _sometimes_ the
planner perform an order by before doing the joins something that was
killing the performance of the app.
I think it could be a problem of the web app, I turned on the 'csvlog'
to diagnose the problem and I found that at the same time the query get
executes one hundred times aprox.
To me it does not look the query is executed one hundred times, it looks
one hundred temp files are created for the query.
This is what got logged at the csv (look at the time, different seconds):
2014-11-20 09:01:18.493
COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35849,"SELECT",2014-11-20
08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.103510"", size 484",,,,,,"
2014-11-20 09:01:18.496
COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35850,"SELECT",2014-11-20
08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.34356"", size 220",,,,,,"
[...]
As you can see from above it creates a lots of temp files for the same
query.
So what is the size of the dataset you are working with and what is the
size of the dataset you expect to return?
What are the hardware specs for the machine you are using, in particular
the amount of memory?
Are there other resource intensive programs running on this machine?
Slds.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
