Well I went ahead and ran the same query and this is my result:
EXPLAIN ANALYZE SELECT p.id from dbmail_physmessage p where p.id NOT IN
(select physmessage_id from dbmail_headervalue);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on dbmail_physmessage p (cost=29372.58..510886373.58 rows=32249
width=8) (actual time=42115240.380..42115240.380 rows=0 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=29372.58..42869.56 rows=937598 width=8) (actual
time=0.027..358.673 rows=478536 loops=65186)
-> Seq Scan on dbmail_headervalue (cost=0.00..24313.98 rows=937598
width=8) (actual time=0.026..940.275 rows=947228 loops=1)
Total runtime: 42115290.134 ms
(6 rows)
Thats right, nearly 12 hours!
Should I go through the database and just delete all the indexes and
constraints and then re add them?
Since it is 8.2 autovacuum does not appear in the log? How can I even tell if
the autovacuum is running correctly?
Thanks,
Eric
> From: [EMAIL PROTECTED]
> To: [email protected]
> Subject: Re: [Dbmail] Restore DB & get better performance
> Date: Fri, 14 Sep 2007 13:09:24 +0200
>
> On Freitag, 14. September 2007 06:05 Eric Hiller wrote:
> > I am unable to find the post you mentioned. I am looking for a way
> > to renew the structure of my database. and clean up existing data.
>
> Sorry, see thread "massive runtime improvement for dbmail-util", my 2nd
> mail.
>
> http://www.nabble.com/massive-runtime-improvement-for-dbmail-util---tf4332845.html#a12340159
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc ----- http://it-management.at
> // Tel: 0676/846 914 666 .network.your.ideas.
> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0
> // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
_________________________________________________________________
Can you find the hidden words? Take a break and play Seekadoo!
http://club.live.com/seekadoo.aspx?icid=seek_wlmailtextlink
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
