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

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

 



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;
                                              Table "public.my_table"
             Column              |           Type           |                        Modifiers
---------------------------------+--------------------------+----------------------------------------------------------
 id                              | bigint                   | not null default nextval('my_table_id_seq'::regclass)
 devid| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| timestamp with time zone |
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| integer                  | not null
 column_name| text                     | not null
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | not null default 0
 column_name| integer                  | default 0
 column_name| integer                  | default 0
 column_name| integer                  | default 0
 column_name| integer                  | default 0
 column_name| integer                  | default 0
 column_name| integer                  | default 0
 end_date                        | timestamp with time zone |

Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)
    "my_table_date_idx" btree (date)
    "my_table_device_idx" btree (devid)
    "end_date_idx" btree (end_date)
Foreign-key constraints:
    "fk_aaaaa" FOREIGN KEY (devid) REFERENCES device_data(id)
Referenced by:
    TABLE "table1" CONSTRAINT "application_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table2" CONSTRAINT "configuration_changes_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table3" CONSTRAINT "fk_57hmvnx423bw9h203260r8gic" FOREIGN KEY (my_table) REFERENCES my_table(id)
    TABLE "table3" CONSTRAINT "interface_change_my_table_fk" FOREIGN KEY (my_table) REFERENCES my_table(id)
    TABLE "table4" CONSTRAINT "my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id) ON DELETE CASCADE
    TABLE "table5" CONSTRAINT "my_table_report_my_table_fk" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table6" CONSTRAINT "my_table_to_policy_change_my_table_foreign_key" FOREIGN KEY (my_table) REFERENCES my_table(id)
    TABLE "table7" CONSTRAINT "network_object_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table8" CONSTRAINT "orig_nat_rule_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table9" CONSTRAINT "risk_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table10" CONSTRAINT "rule_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)
    TABLE "table11" CONSTRAINT "service_change_my_table_id_fkey" FOREIGN KEY (my_table_id) REFERENCES my_table(id)

As you can see alot of other tables uses the id col as a foreign key which make the delete much slower.

Solution I tried for the query : 

delete  from my_table where end_date <= to_date('12/12/2018','DD/MM/YYYY') and end_date > to_date('11/12/2018','DD/MM/YYYY');

                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 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/2018'::text, 'DD/MM/YYYY'::text)) AND (end_date > to_date('11/12/2018'::text, 'DD/MM/YYYY'::text)))
         Rows Removed by Filter: 40253
 Planning time: 0.210 ms
 Trigger for constraint table1: time=14730.816 calls=1572864
 Trigger for constraint table2: time=30718.084 calls=1572864
 Trigger for constraint table3: time=28170.363 calls=1572864
 Trigger for constraint table4: time=29573.681 calls=1572864
 Trigger for constraint table5: time=29629.263 calls=1572864
 Trigger for constraint table6: time=29628.489 calls=1572864
 Trigger for constraint table7: time=29798.121 calls=1572864
 Trigger for constraint table8: time=29645.705 calls=1572864
 Trigger for constraint table9: time=29657.177 calls=1572864
 Trigger for constraint table10: time=29487.054 calls=1572864
 Trigger for constraint table11: time=30010.978 calls=1572864
 Trigger for constraint table12: time=26383.924 calls=1572864
 Execution time: 350603.047 ms
(18 rows)

-----------------------

DELETE FROM my_table WHERE id IN (select id from my_table where end_date <= to_date('12/12/2018','DD/MM/YYYY') and end_date > to_date('11/12/2018','DD/MM/YYYY'));



                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on my_table  (cost=92522.54..186785.27 rows=1572738 width=12) (actual time=9367.477..9367.477 rows=0 loops=1)
   ->  Hash Join  (cost=92522.54..186785.27 rows=1572738 width=12) (actual time=2871.906..5503.732 rows=1572864 loops=1)
         Hash Cond: (my_table.id = my_table_1.id)
         ->  Seq Scan on my_table  (cost=0.00..49052.16 rows=1613116 width=14) (actual time=0.004..669.184 rows=1613117 loops=1)
         ->  Hash  (cost=65183.32..65183.32 rows=1572738 width=14) (actual time=2871.301..2871.301 rows=1572864 loops=1)
               Buckets: 131072  Batches: 32  Memory Usage: 3332kB
               ->  Seq Scan on my_table my_table_1  (cost=0.00..65183.32 rows=1572738 width=14) (actual time=0.009..2115.826 rows=1572864 loops=1)
                     Filter: ((end_date <= to_date('12/12/2018'::text, 'DD/MM/YYYY'::text)) AND (end_date > to_date('11/12/2018'::text, 'DD/MM/YYYY'::text)))
                     Rows Removed by Filter: 40253
 Planning time: 0.419 ms
 Trigger for constraint my_table_id_fkey: time=14291.206 calls=1572864
 Trigger for constraint table2_fk: time=29171.591 calls=1572864
 Trigger for constraint table3_fk: time=26356.711 calls=1572864
 Trigger for constraint table4_fk: time=27579.694 calls=1572864
 Trigger for constraint table5_fk: time=27537.491 calls=1572864
 Trigger for constraint table6_fk: time=27574.169 calls=1572864
 Trigger for constraint table7_fk: time=27716.636 calls=1572864
 Trigger for constraint table8_fk: time=27780.192 calls=1572864
....
....

 Execution time: 333166.233 ms ~ 5.5 minutes
(23 rows)


Loading into a temp table the data isnt option because I cant truncate the table because of all the dependencies...

Any idea what else can I check ?


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux