Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query performance issue

Query performance issue

From
Dheeraj Sonawane
Date:

Hello all,

 

While executing the join query on the postgres database we have observed sometimes randomly below query is being fired which is affecting our response time.

 

Query randomly fired in the background:-
SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname='pg_catalog' AND ( proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname = 'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_lseek64' or proname = 'lo_tell' or proname = 'lo_tell64' or proname = 'loread' or proname = 'lowrite' or proname = 'lo_truncate' or proname = 'lo_truncate64')

 

Query intended to be executed:-

SELECT a.* FROM tablename1 a INNER JOIN users u ON u.id = a.user_id INNER JOIN tablename2 c ON u.client_id = c.id WHERE u.external_id = ? AND c.name = ? AND (c.namespace = ? OR (c.namespace IS NULL AND ? IS NULL))

 

Postgres version 11

 

Below are my questions:-

  1. Is the query referring pg_catalog fired by postgres library implicitly?
  2. Is there any way we can suppress this query?

 

 

Thanks and regards,

Dheeraj Sonawane

 

Mastercard

| mobile +917588196818


 

CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you.
Attachment

Re: Query performance issue

From
Tom Lane
Date:
Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com> writes:
> While executing the join query on the postgres database we have observed sometimes randomly below query is being
firedwhich is affecting our response time. 

> Query randomly fired in the background:-
> SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n WHERE p.pronamespace=n.oid AND
n.nspname='pg_catalog'AND ( proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname =
'lo_unlink'or proname = 'lo_lseek' or proname = 'lo_lseek64' or proname = 'lo_tell' or proname = 'lo_tell64' or proname
='loread' or proname = 'lowrite' or proname = 'lo_truncate' or proname = 'lo_truncate64') 

That looks very similar to libpq's preparatory lookup before executing
large object accesses (cf lo_initialize in fe-lobj.c).  The details
aren't identical so it's not from libpq, but I'd guess this is some
other client library's version of the same thing.

> Query intended to be executed:-
> SELECT a.* FROM tablename1 a INNER JOIN users u ON u.id = a.user_id INNER JOIN tablename2 c ON u.client_id = c.id
WHEREu.external_id = ? AND c.name = ? AND (c.namespace = ? OR (c.namespace IS NULL AND ? IS NULL)) 

It is *really* hard to believe that that lookup query would make any
noticeable difference on response time for some other session, unless
you are running the server on seriously underpowered hardware.

It could be that you've misinterpreted your data, and what is actually
happening is that that other session has completed its lookup query
and is now doing fast-path large object reads and writes using the
results.  Fast-path requests might not show up as queries in your
monitoring, but if the large object I/O is sufficiently fast and
voluminous maybe that'd account for visible performance impact.

>   2.  Is there any way we can suppress this query?

Stop using large objects?  But the alternatives won't be better
in terms of performance impact.  Really, if this is a problem
for you, you need a beefier server.  Or split the work across
more than one server.

            regards, tom lane