Thread: Perfomance issue. statement in the log file..

On 2/23/2016 11:57 AM, Bala Venkat wrote:
> Dear All -
>
>        We are seeing lot of these statements in the log file. We have
> 2 functions .  Submit and update .  Between submit and update which
> will int the process, the messages are appearning. The process is very
> slow. We don't know if this the main reason for the performance issue
>
> Can you please help ? if any one has come across  this issue and
> resolved. Appreciate your time.
>
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid
> = $1 AND adn
> um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)       LIKE
> '%nextval(%'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.059 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.117 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 1.087 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT attnotnull FROM pg_catalog.pg_attribute
> WHERE attrelid =
> $1 AND attnum = $2
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'

...
Having done a cursory search for "SELECT attnotnull FROM
pg_catalog.pg_attribute WHERE attrelid ="
It appears your potential culprit is a JDBC or ODBC (or ORM managed)
connection querying meta-data over and over.
To identify connection information, back in 2010 [so... your mileage may
vary] Tom suggested the use of:

See log_connections and log_disconnections.  You probably want to
add PID to log_line_prefix, too, so that you can associate different
log entries for the same session.


Version of Postgres and the execution environment of the 2 functions.
[e.g. pgsql, PL/?, code etc] might provide more input to help identify a
more specific answer.

Roxanne

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

On 02/23/2016 11:57 AM, Bala Venkat wrote:
> Dear All -
>
>         We are seeing lot of these statements in the log file. We have 2
> functions .  Submit and update .  Between submit and update which will
> int the process, the messages are appearning. The process is very slow.
> We don't know if this the main reason for the performance issue
>
> Can you please help ? if any one has come across  this issue and
> resolved. Appreciate your time.

Help will require more information. For future reference you may want to
take a look at:

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

In mean time and as a starting point:

What version of OS and Postgres?

What client is generating the queries?

Define slow and how are you determining it?

Can you show the contents of the two functions?

>
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
> $1 AND adn
> um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)       LIKE '%nextval(%'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.059 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.117 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 1.087 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
> attrelid =
> $1 AND attnum = $2
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.037 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.081 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.120 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
> $1 AND adn
> um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)       LIKE '%nextval(%'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.028 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.058 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.163 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
> attrelid =
> $1 AND attnum = $2
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.032 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.074 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.118 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
> $1 AND adn
> um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)       LIKE '%nextval(%'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.027 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.058 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.161 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> execute <unnamed>: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
> attrelid =
> $1 AND attnum = $2
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
> db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.032 ms
> 2016-02-23 18:11:24 GMT 172.26.22.145(52330)  user=postgres ,
> db=testLOG:  duration: 1210.331 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 1.230 ms
> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
> duration: 0.083 ms


--
Adrian Klaver
adrian.klaver@aklaver.com