Dheeraj Sonawane <Dheeraj.Sonawane@xxxxxxxxxx> 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
