Re: Improving performance of select query

select count(*) from <table> 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 (<id1>,<id2>,..); *
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 <mbjamil92(at)gmail(dot)com>
wrote:

> I think you can also increase the query performance by creating indexes?
>
> On Mon, 14 Dec 2020 at 11:36, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>>
>>
>> > On Dec 14, 2020, at 4:47 AM, Thomas Kellerer <shammat(at)gmx(dot)net> 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 (<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
>> >
>> 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..id10000)
>>
>> >
>>
>>
>>
>>