Hi list,
I've been running this simple delete since yesterday afternoon :
> db=# explain delete from message where datetime < '2009-03-03';
> Seq Scan on message (cost=0.00..34131.95 rows=133158 width=6)
> Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)
There is no index on that column, so a seqscan is fine. But it really
shouldn't take > 15 hours to delete :
> db=# select count(*) from message where datetime < '2009-03-03';
> 184368
> Time: 751.721 ms
>
> db=# select count(*) from message;
> 1079463
> Time: 593.899 ms
>
> db=# select pg_size_pretty(pg_relation_size('message'));
> 161 MB
> Time: 96.062 ms
>
> db=# \o /dev/null
> db=# select * from message where datetime < '2009-03-03';
> Time: 4975.123 ms
Most of the time, there is no other connection to that database. This is on an
oldish laptop. atop reports 100% cpu and about 24KB/s of writes for postgres.
Machine is mostly idle (although I did run a multi-hours compile during the
night). Nothing looks wrong in postgres logs.
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc
(Gentoo 4.3.2-r3 p1.6, pie-10.1.5) 4.3.2
postgresql.conf :
> max_connections = 100
> shared_buffers = 24MB
> max_fsm_pages = 153600
> log_destination = 'stderr'
> logging_collector = on
> log_directory = '/var/log/postgres/'
> log_filename = '%Y-%m-%d_%H%M%S.log'
> log_rotation_size = 100MB
> log_min_duration_statement = 30000
> log_line_prefix = '%t %d %p '
> datestyle = 'iso, mdy'
> lc_messages = 'C'
> lc_monetary = 'C'
> lc_numeric = 'C'
> lc_time = 'C'
> default_text_search_config = 'pg_catalog.english'
Not sure what to look at to debug this further (I could work around the
problem with pg_dump + grep, but that's beside the point). Any idea ?
Thanks.
--
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325
Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@xxxxxxxxxxxxxxxxx and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
