Re: Query performance issue

Dheeraj Sonawane <Dheeraj(dot)Sonawane(at)ethoca(dot)com> writes:
> 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')

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 WHERE u.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