Hi Sékine, You're right : my question is why the planner doesn't use the index ! My DELETE statements have WHERE clause like : start_date<1346486100000. They are executed to delete too old rows. My postgresql version is 8.4. Below is an example of a table (they all have the same structure) :
CREATE TABLE agg_t100_outgoing_a39_src_net_f5 ( total_pkts bigint, end_date bigint, src_network inet, start_date bigint, total_flows bigint, total_bytes bigint ) WITH ( OIDS=FALSE ); CREATE INDEX agg_t100_outgoing_a39_src_net_f5_end_date ON agg_t100_outgoing_a39_src_net_f5 USING btree (end_date); CREATE INDEX agg_t100_outgoing_a39_src_net_f5_start_date ON agg_t100_outgoing_a39_src_net_f5 USING btree (start_date); I have investigated in the pg_stat_all_tables table and it seems the autovaccum / autoanalyze don't do their job. Many tables have no last_autovacuum / last_autoanalyze dates ! So the planner doesn't have fresh stats to decide. Don't you think it could be a good reason for slow DELETE ? In this case, the trouble could come from the autovaccum configuration. Regards, Sylvain ----- Mail original ----- > Hi Sylvain, > Might sound like a nasty question, and gurus will correct me if I'm > wrong, but first thing to investigate is why the index is not used : > - You have 2/3 million rows per table so the planner should use the > index. Seqscan is prefered for small tables. > - Maybe the WHERE clause of your DELETE statement doesn't make use of > your start and end date columns ? If so, in which order ? > Please, provide with your Pg version and the table setup with the > index. > Regards, > Sekine > 2012/10/16 Sylvain CAILLET < scail...@alaloop.com > > > Hi to all, > > > I've got a trouble with some delete statements. My db contains a > > little more than 10000 tables and runs on a dedicated server > > (Debian > > 6 - bi quad - 16Gb - SAS disks raid 0). Most of the tables contains > > between 2 and 3 million rows and no foreign keys exist between > > them. > > Each is indexed (btree) on start_date / end_date fields (bigint). > > The Postgresql server has been tuned (I can give modified values if > > needed). > > > I perform recurrent DELETE upon a table subset (~1900 tables) and > > each time, I delete a few lines (between 0 and 1200). Usually it > > takes between 10s and more than 2mn. It seems to me to be a huge > > amount of time ! An EXPLAIN ANALYZE on a DELETE shows me that the > > planner uses a Seq Scan instead of an Index Scan. Autovaccum is on > > and I expect the db stats to be updated in real time (pg_stats file > > is stored in /dev/shm RAM disk for quick access). > > > Do you have any idea about this trouble ? > > > Sylvain Caillet > > > Bureau : + 33 5 59 41 51 10 > > > scail...@alaloop.com > > > ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart > > > www.alaloop.com >
