Re: [Dspam-user] [pgsql] purge-pe script, order of commands

[Stevan B. <st...@ba...>]

On Thu, 29 Jul 2010 23:17:05 +0200
Marcin Mirosław <ma...@me...> wrote:

> Hi everebody!
>
Hello Mirosław,


nice to see you on the DSPAM mailing list. One Gentoo user (I have exchanged mails with you in the past but over another email, but I think you know who I am) more here :)


> I'm starting play with DSPAM and still learn how to use it in best way.
> I'm loooking into purge-pe.sql script, there is:
> VACUUM ANALYSE dspam_token_data;
> VACUUM ANALYSE dspam_signature_data;
> 
> REINDEX TABLE dspam_token_data;
> REINDEX TABLE dspam_signature_data;
> 
> Meseems vacuum should be done after reindex table, to get statistics
> from newly created index. Second thing is reindexing, isn't problematic
> that reindex locks table? Imho vacuum and reindex can be done in this
> way (which theoretically should be faster ):
> - drop constraints [1]
> - create unique indexes
> 
> BEGIN;
> CREATE unique index dspam_signature_data_uid_key_TEMP on .... ;
> -- sadly, create index concurrently can't be used inside transaction
> DROP index dspam_signature_data_uid_key
> ALTER index dspam_signature_data_uid_key_TEMP rename to
> dspam_signature_data_uid_key;
> COMMIT;
> VACUUM ANALYSE dspam_signature_data;
> 
> What do you think about it?
> 
To be honest: I think all that vacuum and reindex should go out of the purge script for PostgreSQL. Would make things much easier if all what the purge script does is to remove old tokens and leave it up to the DBA to choose when and how and why to vacuum and reindex.


> Regards,
> Marcin Mirosław
> 
-- 
Kind Regards from Switzerland,

Stevan Bajić