Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Purpose and usage of VACUUM and ANALYZE)

Sample Question


Choose the three best explanations for ANALYZE.

  1. Users can run ANALYZE on the tables they own.

  2. Running ANALYZE is important to optimize performance (speed) when executing SQL such as SELECT.

  3. ANALYZE is very intensive because it examines all the data in the target table. It is preferable to execute it at night, when there is little database activity.

  4. The VACUUM command also automatically runs ANALYZE on the same table.

  5. When you run autovacuum, ANALYZE runs automatically as well as VACUUM.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Indexes are created to improve performance when executing SQL commands such as SELECT. However, in some cases it is faster to perform a full scan even if there is an index, and in other cases it is not simply possible to determine which of the several indexes should be used to make it faster.

Disk access has the greatest impact on SQL execution speed. To estimate how much disk access is likely to be required for each of the several possible execution plans, the number of data items in each table, the amount of data spread in columns in the table, etc. are to be checked.

ANALYZE retrieves such information about the state of the table. It is desirable to run ANALYZE once on every table, and to run it again every time there is an update that significantly affects the number or distribution of data.

ANALYZE samples a small fraction of the data in a table and computes statistics, so it is quick to complete. When running ANALYZE on a table name, for example from the psql command line, the table owner or superuser can run ANALYZE.

If ANALYZE is specified as an option when running a VACUUM command, VACUUM and ANALYZE will run concurrently, but if no option is specified, only VACUUM will run.

Autovacuum performs an automatic VACUUM operation on tables that have reached a certain level of change, but it also runs ANALYZE at the same time.

So the correct answers are A, B, and E.