Search Postgresql Archives

Re: Improving performance of select query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
I think you can also increase the query performance by creating indexes?

On Mon, 14 Dec 2020 at 11:36, Rob Sargent <robjsargent@xxxxxxxxx> wrote:


> On Dec 14, 2020, at 4:47 AM, Thomas Kellerer <shammat@xxxxxxx> 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)

>




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux