Re: trying to delete most of the table by range of date col

Hi jefff, I tried every solution that I checked on net. I cant disable foreign keys or indexes. Trying to have better performance by just changing the query / changing parameters. ‫בתאריך יום ב׳, 3 בספט׳ 2018 ב-18:25 מאת ‪Jeff Janes‬‏ <‪ jeff.ja...@gmail.com‬‏>:‬ > > > >> >> 4)delete in chunks :

Re: trying to delete most of the table by range of date col

> > 4)delete in chunks : > do $$ > declare > rec integer; > begin > select count(*) from my_table into rec where end_date <= > to_date('12/12/2018','DD/MM/') and end_date > > to_date('11/12/2018','DD/MM/'); > while rec > 0 loop > DELETE FROM my_Table WHERE id IN (select id from my_tablewher

Re: trying to delete most of the table by range of date col

Hi > > I understand about having to deal with a bad design. How big is the table > "select pg_size_pretty(pg_table_size(table_name)).? If the table is not > that large relative to the IOPS on your disk system, another solution is to > add a binary column IS_DELETED to the table and modify the que

Re: trying to delete most of the table by range of date col

I'm not responsible for this design but I'm trying to improve it. Using partition isnt an option because partitions doesnt support foreign key. Moreover, most queries on all those tables uses the id col of the main table. ‫בתאריך יום ב׳, 3 בספט׳ 2018 ב-14:09 מאת ‪Carrie Berlin‬‏ <‪ berlincar...@g

Re: trying to delete most of the table by range of date col

This is a terribley inflexible design, why so many foreign keys? If the table requires removing data, rebuild with partitions. Parent keys should be in reference tables, not in fact table. On Mon, Sep 3, 2018 at 04:51 Mariel Cherkassky wrote: > Cant drop foreign keys, there are too much. > > ‫

Re: trying to delete most of the table by range of date col

I checked, the results : 1)explain (analyze,buffers) delete from my_table where end_date <= to_date('12/12/2018','DD/MM/') and end_date > to_date('11/12/2018','DD/MM/'); QUERY PLAN --

Re: trying to delete most of the table by range of date col

On Mon, Sep 03, 2018 at 11:17:58AM +0300, Mariel Cherkassky wrote: > Hi, > I already checked and on all the tables that uses the id col of the main > table as a foreign key have index on that column. > > So, it seems that the second solution is the fastest one. It there a reason > why the delete c

Re: trying to delete most of the table by range of date col

Cant drop foreign keys, there are too much. ‫בתאריך יום ב׳, 3 בספט׳ 2018 ב-11:35 מאת ‪Sergei Kornilov‬‏ <‪s...@zsrv.org ‬‏>:‬ > Hello > > > Delete on my_table (cost=0.00..65183.30 rows=1573862 width=6) (actual > time=5121.344..5121.344 rows=0 loops=1) > >-> Seq Scan on my_table (cost=0.00

Re: trying to delete most of the table by range of date col

Hello >  Delete on my_table  (cost=0.00..65183.30 rows=1573862 width=6) (actual > time=5121.344..5121.344 rows=0 loops=1) >    ->  Seq Scan on my_table  (cost=0.00..65183.30 rows=1573862 width=6) > (actual time=0.012..2244.393 rows=1572864 loops=1) >          Filter: ((end_date <= to_date('12/12

Re: trying to delete most of the table by range of date col

Hi, I already checked and on all the tables that uses the id col of the main table as a foreign key have index on that column. I tried all the next 4 solutions : 1)delete from my_table where end_date <= to_date('12/12/2018','DD/MM/') and end_date > to_date('11/12/2018','DD/MM/'); Executi

Re: trying to delete most of the table by range of date col

Am 03.09.2018 um 09:06 schrieb Justin Pryzby: Note, I believe it's planned in the future for foreign keys to support referenes to partitioned tables, at which point you could just DROP the monthly partition...but not supported right now. the future is close, that's possible in 11 ;-) Regard

Re: trying to delete most of the table by range of date col

On Mon, Sep 03, 2018 at 09:27:52AM +0300, Mariel Cherkassky wrote: > I'm trying to find the best way to delete most of the table but not all of it > according to a range of dates. > Indexes: > "end_date_idx" btree (end_date) > Referenced by: > TABLE "table1" CONSTRAINT "application_change

trying to delete most of the table by range of date col

Hi, I have a big table (with 1.6 milion records). One of the columns is called end_date and it`s type is timestamp. I'm trying to find the best way to delete most of the table but not all of it according to a range of dates. The table structure : afa=# \d my_table;