From: | Karthik Shivashankar <kshivashank(at)sandvine(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Improving performance of select query |
Date: | 2020-12-14 11:38:37 |
Message-ID: | 20c57bfb287749c2a5ff22d7ddd3df93@sandvine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 (<id1>,<id2>,..);
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2020-12-14 11:47:47 | Re: Improving performance of select query |
Previous Message | Hemil Ruparel | 2020-12-14 08:36:45 | Re: Dynamic procedure execution |