Re: Dynamic procedure execution
Not to be disrespectful, but you need to at least struggle to find the answers yourself before posting here. On Mon, Dec 14, 2020 at 12:02 PM Muthukumar.GK wrote: > Hi David, > > As I am not bit Clea, let me know what I have to do. If possible, please > re- write my program. > > Regards > Muthu > > On Mon, Dec 14, 2020, 11:43 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, December 13, 2020, Muthukumar.GK >> wrote: >> >>> >>> v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"'; >>> >>> >>> >>> EXECUTE (v_query); >>> >>> >>> >> You put the pl/pgsql OPEN command into a string and sent it to the SQL >> engine via EXECUTE and the SQL engine is complaining that it has no idea >> what you want it to do. >> >> David J. >> >>
Improving performance of select query
Hi, I have a postgres(v9.5) table named customer holding 1 billion rows. It is not partitioned but it has an index against the primary key (integer). I need to keep a very few records (say, about 10k rows) and remove everything else. insert into customer_backup select * from customer where customer_id in (,,..); If I go for something like above I'm afraid the insert-select may take a very long time as when I ran select count(*) from customer; it is taking about 45 minutes to return the count. Are there ways to improve the efficiency of the insert-select by , say, tuning some configurations related to memory to improve the efficiency ? This is a box with 96GB of RAM overall and I can stop all the data load and DML operations if needed. But need a way to run this query as much efficiently as possible Thanks and Regards, Karthik Disclaimer: This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is considered confidential, proprietary, sensitive and/or otherwise legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
Re: Improving performance of select query
Karthik Shivashankar schrieb am 14.12.2020 um 12:38: > I have a postgres(v9.5) table named customer holding 1 billion rows. > It is not partitioned but it has an index against the primary key > (integer). I need to keep a very few records (say, about 10k rows) > and remove everything else. > > /insert into customer_backup select * from customer where customer_id in > (,,..); / > > > > If I go for something like above I'm afraid the insert-select may take a very > long time as when I ran > > /select count(*) from customer;/ > > it is taking about 45 minutes to return the count. Well, you need to compare the time with the same condition you use in your CREATE TABLE .. AS SELECT statement, e.g.: select count(*) from customer where id in (); Or: explain (analyze) select * from customer where id in (); Regards Thomas
Re: Dynamic procedure execution
On Sunday, December 13, 2020, Muthukumar.GK wrote: > Hi David, > > As I am not bit Clea, let me know what I have to do. If possible, please > re- write my program. > Like the SQL executor, I have no idea what you are trying to do there. Neither the text variable, nor the cursor, nor plpgsql for that matter, provide any benefit to executing “select from table”. David J.
Re: Dynamic procedure execution
On Monday, December 14, 2020, David G. Johnston wrote: > > On Sunday, December 13, 2020, Muthukumar.GK wrote: > >> Hi David, >> >> As I am not bit Clea, let me know what I have to do. If possible, please >> re- write my program. >> > > Like the SQL executor, I have no idea what you are trying to do there. > Neither the text variable, nor the cursor, nor plpgsql for that matter, > provide any benefit to executing “select from table”. > > That said, kf you read the “see also” part of the FETCH documentation you will see that DECLARE is the SQL way to dpecify a cursor, not OPEN. David J.
Re: Dynamic procedure execution
On 12/13/20 9:59 PM, Muthukumar.GK wrote: Hi team, When I am trying to implement belwo dynamic concept in postgreSql, getting some error. Kindly find the below attached program and error. Please advise me what is wrong here.. CREATE OR REPLACE PROCEDURE DynamicProc() AS $$ DECLARE v_query TEXT; C1 refcursor := 'result1'; begin v_query := ''; v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"'; EXECUTE (v_query); END; $$ Language plpgsql; Calling procedure :- CALL DynamicProc(); FETCH ALL IN "result1"; Error :- -- ERROR: syntax error at or near "OPEN" LINE 1: OPEN C1 FOR SELECT * FROM public."Los_BankInfo" ^ QUERY: OPEN C1 FOR SELECT * FROM public."Los_BankInfo" CONTEXT: PL/pgSQL function dynamicproc() line 9 at EXECUTE SQL state: 42601 Two things: 1) The error is from a different version of the procedure then the code. The table name is different. Can't be sure that this is the only change. So can you synchronize your code with the error. 2) Take a look here: https://www.postgresql.org/docs/12/plpgsql-cursors.html 42.7.2. Opening Cursors For why OPEN is plpgsql specific and how to use it. Regards Muthukumar.gk -- Adrian Klaver adrian.kla...@aklaver.com
Mitigating impact of long running read-only queries
https://www.postgresql.org/docs/current/sql-set-transaction.html "The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups." If I use these three properties together as below, would WAL buildup be prevented, or will the system still be causing a backup of the WAL for logical replication during the entire duration of my long reporting query? BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; --long report query commit;
Re: Improving performance of select query
> On Dec 14, 2020, at 4:47 AM, Thomas Kellerer wrote: > > Karthik Shivashankar schrieb am 14.12.2020 um 12:38: >> I have a postgres(v9.5) table named customer holding 1 billion rows. >> It is not partitioned but it has an index against the primary key >> (integer). I need to keep a very few records (say, about 10k rows) >> and remove everything else. >> >> /insert into customer_backup select * from customer where customer_id in >> (,,..); / >> >> >> >> If I go for something like above I'm afraid the insert-select may take a >> very long time as when I ran >> >> /select count(*) from customer;/ >> >> it is taking about 45 minutes to return the count. > > Well, you need to compare the time with the same condition you use in your > CREATE TABLE .. AS SELECT statement, > > e.g.: > > select count(*) > from customer > where id in (); > > Or: > > explain (analyze) > select * > from customer > where id in (); > > > Regards > Thomas > As for the actually copy of the specific records, I would ‘where exists’ (even possibly with a temp table of ids) rather than in(id1..id1) >
Re: Improving performance of select query
I think you can also increase the query performance by creating indexes? On Mon, 14 Dec 2020 at 11:36, Rob Sargent wrote: > > > > On Dec 14, 2020, at 4:47 AM, Thomas Kellerer wrote: > > > > Karthik Shivashankar schrieb am 14.12.2020 um 12:38: > >> I have a postgres(v9.5) table named customer holding 1 billion rows. > >> It is not partitioned but it has an index against the primary key > >> (integer). I need to keep a very few records (say, about 10k rows) > >> and remove everything else. > >> > >> /insert into customer_backup select * from customer where customer_id > in (,,..); / > >> > >> > >> > >> If I go for something like above I'm afraid the insert-select may take > a very long time as when I ran > >> > >> /select count(*) from customer;/ > >> > >> it is taking about 45 minutes to return the count. > > > > Well, you need to compare the time with the same condition you use in > your > > CREATE TABLE .. AS SELECT statement, > > > > e.g.: > > > > select count(*) > > from customer > > where id in (); > > > > Or: > > > > explain (analyze) > > select * > > from customer > > where id in (); > > > > > > Regards > > Thomas > > > As for the actually copy of the specific records, I would ‘where exists’ > (even possibly with a temp table of ids) rather than in(id1..id1) > > > > > > >
Re: Improving performance of select query
select count(*) from is probably not using the index that your insert/select would, so I would not use that as a test for performance. If customer_backup has an index, the insert-select will be performance-limited by updating that index. If you can do a *create table customer_backup* as *select * from customer where customer_id in (,,..); * I expect it to run quite fast as long as customer_id is indexed and analyzed. On Mon, Dec 14, 2020 at 9:37 AM Muhammad Bilal Jamil wrote: > I think you can also increase the query performance by creating indexes? > > On Mon, 14 Dec 2020 at 11:36, Rob Sargent wrote: > >> >> >> > On Dec 14, 2020, at 4:47 AM, Thomas Kellerer wrote: >> > >> > Karthik Shivashankar schrieb am 14.12.2020 um 12:38: >> >> I have a postgres(v9.5) table named customer holding 1 billion rows. >> >> It is not partitioned but it has an index against the primary key >> >> (integer). I need to keep a very few records (say, about 10k rows) >> >> and remove everything else. >> >> >> >> /insert into customer_backup select * from customer where customer_id >> in (,,..); / >> >> >> >> >> >> >> >> If I go for something like above I'm afraid the insert-select may take >> a very long time as when I ran >> >> >> >> /select count(*) from customer;/ >> >> >> >> it is taking about 45 minutes to return the count. >> > >> > Well, you need to compare the time with the same condition you use in >> your >> > CREATE TABLE .. AS SELECT statement, >> > >> > e.g.: >> > >> > select count(*) >> > from customer >> > where id in (); >> > >> > Or: >> > >> > explain (analyze) >> > select * >> > from customer >> > where id in (); >> > >> > >> > Regards >> > Thomas >> > >> As for the actually copy of the specific records, I would ‘where exists’ >> (even possibly with a temp table of ids) rather than in(id1..id1) >> >> > >> >> >> >>
Re: Improving performance of select query
> On Dec 14, 2020, at 10:37 AM, Muhammad Bilal Jamil > wrote: > > I think you can also increase the query performance by creating indexes? > > > OP said there was a key on the target (large) table. I’m not sure there’s much of a win in indexing 10K ids. > >
PostgreSQL @ FOSDEM 2021 - Call for Papers
Call for Proposals We are happy to announce that FOSDEM is hosting a virtual PostgreSQL Devroom at FOSDEM 2021. Next year’s conference will take place on the 6th and 7th of February, with the PostgreSQL Devroom being on Saturday 6th. Information about FOSDEM is available at the official website at https://www.fosdem.org/. The in-person events in previous years attracted more than 8000 participants, expect more people joining for an online event. We are now looking for PostgreSQL related talks from both experienced and new speakers. Topics of Interest * Developing applications with or for PostgreSQL * Administering large scale PostgreSQL installations * Case studies and/or success stories of PostgreSQL deployments (or interesting failures) * Tools and utilities * PostgreSQL internals hacking * Community and local user groups * Tuning and performance improvements * Migration from other database systems * Replication, clustering and high availability * Recovery and backup strategies * Benchmarking and hardware * PostgreSQL related products * DevOps and continuous deployment/configuration/integration around PostgreSQL * Any other PostgreSQL related topic Call for Papers Committee * Alicja Kucharczyk * Georgios Kokolatos * Andreas Scherbaum About PostgreSQL PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extension. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations. About FOSDEM Official website: https://fosdem.org/2021/ FOSDEM Code of Conduct: https://fosdem.org/2021/practical/conduct/ FOSDEM is a free and non-commercial event organised by the community for the community. The goal is to provide free and open source software developers and communities a place to meet to: * Get in touch with other developers and projects * Be informed about the latest developments in the free software world * Be informed about the latest developments in the open source world * Attend interesting talks and presentations on various topics by project leaders and committers * To promote the development and benefits of free software and open source solutions * Participation and attendance is totally free, though the organisers gratefully accept donations and sponsorship Essential Information The devroom will be held on 6th of February 2021, online Submission link: https://penta.fosdem.org/submission/FOSDEM21 Talk format: * 25 min of content + 5 min of questions * 45 min of content + 10 min of questions If you have submitted for previous FOSDEM editions, remember to use your already existing account on Pentabarf. Make sure to fill out the 'person' details. We need a name, photo, biography and contact information. Online rules The reference time will be Brussels local lime (CET). Talks must be pre-recorded in advance, and will be streamed during the event. This is a hard requirement from the FOSDEM organizers for which we can not make an exception. Q/A session will be live. A facility will be provided for attendees to chat between themselves. A facility will be provided for attendees to submit questions. Submission Guidelines If you would like to give a talk, present a project or show off some coding skills, we are looking forward to receiving your application. Submission platform: https://penta.fosdem.org/submission/FOSDEM21 Deadline: /26th of December 2020/ Announcement of selected talks: /31st of December 2020/ Be sure to properly fill your Pentabarf profile (Person) with: * Your name * A speaker bio * Your contact information (for the organizers) Then submit your talk (or event) Pentabarf Notes * "talks" are named "events" in Pentabarf * Track must be "PostgreSQL Devroom" * Duration must be "00:25:00" or "00:50:00" * Event type must be "Lecture" * Abstract is the text that goes in the FOSDEM booklet Volunteers We will also call for volunteers to help us run the event and help us with the devroom operation. You can get in touch with the organizers at: cont...@fosdempgday.org Organizers You can reach out directly to the organ
