From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Improving performance of select query |
Date: | 2020-12-14 11:47:47 |
Message-ID: | b9277405-f578-bab1-2d4b-ad46d60f8b8b@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 (<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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-12-14 14:41:11 | Mitigating impact of long running read-only queries |
Previous Message | Karthik Shivashankar | 2020-12-14 11:38:37 | Improving performance of select query |