Sets operation which must be done on server.
Syntax:
TPSQLOperation = (poANALYZE, poCLUSTER, poVACUUM, poREINDEX);
property Operation: TPSQLOperation;
Description:
Assign kind of operation to Operation property to execute it on the server by calling Execute method.
Possible values:
poANALYZE
ANALYZE collects statistics about the contents of tables in the database, and stores the results in the system table pg_statistic. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
Assign values to TableName property to specify the target table, on which ANALYXE operation will be executed.
If TableName property is not empty, there is possible to specify even columns list on which operation will be executed. To do this fill ColumnList property.
If TableName property is empty, ANALYZE examines every table in the current database. Else ANALYZE examines only that table. If columns names are given, only the statistics for those columns are collected.
If Verbose property is True, ANALYZE emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well. To proceed this messages write TPSQLDatabase.OnNotice event handler.
It is a good idea to run ANALYZE periodically, or just after making major changes in the contents of a table. Accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing. A common strategy is to run VACUUM and ANALYZE once a day during a low-usage time of day. ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.
poCLUSTER
CLUSTER instructs PostgreSQL to cluster the table specified by TableName property based on the index specified by IndexName property. The index must already have been defined on the table.
When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. If one wishes, one can periodically recluster by issuing the command again.
When a table is clustered, PostgreSQL remembers on which index it was clustered. Thus if IndexName property is empty, server reclusters the table on the same index that it was clustered before.
Moreover, if even TableName property is empty, server reclusters all the tables in the current database that the calling user owns, or all tables if called by a superuser. (Never-clustered tables are not included.) This form of CLUSTER cannot be called from inside a transaction or stored procedure.
When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.
In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the heap page for the first row that matches, all other rows that match are probably already on the same heap page, and so you save disk accesses and speed up the query.
During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes.
Because CLUSTER remembers the clustering information, one can cluster the tables one wants clustered manually the first time, and setup a timed event similar to VACUUM so that the tables are periodically reclustered.
Because the planner records statistics about the ordering of tables, it is advisable to run ANALYZE operation on the newly clustered table. Otherwise, the planner may make poor choices of query plans.
The other way to cluster a table is to use query:
CREATE TABLE newtable AS
SELECT columnlist FROM table ORDER BY columnlist;
which uses the PostgreSQL sorting code in the ORDER BY clause to create the desired order; this is usually much faster than an index scan for unordered data.
poVACUUM
VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.
If TableName property is empty, VACUUM processes every table in the current database. Else, VACUUM processes only that table.
If voANALYZE option is included in VacuumOptions property, server performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts.
If voFULL option is excluded from VacuumOptions property, server simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. If voFull option is included, server does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.
voFREEZE is a special-purpose option that causes tuples to be marked "frozen" as soon as possible, rather than waiting until they are quite old. If this is done when there are no other open transactions in the same database, then it is guaranteed that all tuples in the database are "frozen" and will not be subject to transaction ID wraparound problems, no matter how long the database is left unvacuumed. FREEZE is not recommended for routine use. Its only intended usage is in connection with preparation of user-defined template databases, or other databases that are completely read-only and will not receive routine maintenance VACUUM operations. If Verbose property is True, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well. To proceed this messages, write TPSQLDatabase.OnNotice event handler.
PostgreSQL developers recommend that active production databases be vacuumed frequently (at least nightly), in order to remove expired rows. After adding or deleting a large number of rows, it may be a good idea to issue a VACUUM with voANALYZE option command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.
Including the voFULL option is not recommended for routine use, but may be useful in special cases. An example is when you have deleted most of the rows in a table and would like the table to physically shrink to occupy less disk space. Such operation will usually shrink the table more than a plain VACUUM would.
poREINDEX
REINDEX rebuilds an index based on the data stored in the index's table, replacing the old copy of the index. There are two main reasons to use REINDEX:
- An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.
- The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B-tree indexes in PostgreSQL under certain access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.
REINDEX operation uses as target index specified by IndexName property. If property is empty, operation will try to rebuild all indexes from table specified by TableName property. If both IndexName and TableName values are omitted, then server will recreate all system indexes of a database specified by Database property. Indexes on user tables are not processed. Also, indexes on shared system catalogs are skipped except in stand-alone mode.
REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index. In contrast, DROP INDEX momentarily takes exclusive lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads may be forced into expensive sequential scans. Another important point is that the drop/create approach invalidates any cached query plans that use the index, while REINDEX does not.
See also: ColumnList, Database, IndexName, TableName, VacuumOptions, Verbose, Execute