Query performance issue
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 [cid:[email protected]] 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.
Re: Query performance issue
Dheeraj Sonawane 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
Specific objects backup in PostgreSQL
Dear Team, We received a request from client. They required all functions, stored procedures and triggers backup. can anyone please let me know. How to take backup only above objects. Thanks & Regards, Nikhil, PostgreSQL DBA.
Re: Specific objects backup in PostgreSQL
On Wed, Jul 10, 2024 at 11:05 AM nikhil kumar wrote: > > We received a request from client. They required all functions, stored > procedures and triggers backup. can anyone please let me know. How to take > backup only above objects. > This hardly qualifies as a performance question. You might try the -general list if you want to brainstorm workarounds because pg_dump itself doesn't provide any command line options to give you this specific subset of your database. David J.
Re: Specific objects backup in PostgreSQL
Hi Nikhil, On Wed, Jul 10, 2024 at 8:05 PM nikhil kumar wrote: > Dear Team, > > We received a request from client. They required all functions, stored > procedures and triggers backup. can anyone please let me know. How to take > backup only above objects. > > > Thanks & Regards, > Nikhil, > PostgreSQL DBA. > "pg_dump -s" will export the model, including functions, triggers... as well as tables and views without data. I should have somewhere an old program I wrote on a lazy day to slice this backup into individual objects. I can dig it for you if you need it. Cheers -- Olivier Gautherot
Re: Specific objects backup in PostgreSQL
Hi Nikhil, maybe you can apply some tricks playing with pg_dump -s and pg_restore --list check this link: https://stackoverflow.com/a/13758324/8308381 Regards On 10-07-24 14:05, nikhil kumar wrote: Dear Team, We received a request from client. They required all functions, stored procedures and triggers backup. can anyone please let me know. How to take backup only above objects. Thanks & Regards, Nikhil, PostgreSQL DBA.
Re: Specific objects backup in PostgreSQL
Thank you everyone for your help On Thu, 11 Jul, 2024, 1:34 am Anthony Sotolongo, wrote: > Hi Nikhil, maybe you can apply some tricks playing with pg_dump -s and > pg_restore --list > > > check this link: https://stackoverflow.com/a/13758324/8308381 > > > Regards > > On 10-07-24 14:05, nikhil kumar wrote: > > Dear Team, > > > > We received a request from client. They required all functions, stored > > procedures and triggers backup. can anyone please let me know. How to > > take backup only above objects. > > > > > > Thanks & Regards, > > Nikhil, > > PostgreSQL DBA. >
