[Monitoring Performance - Statistics for tables and columns]
2.11
Choose two correct statements regarding table/column statistics.
-
pg_class handles information on views and indexes as well as tables.
-
pg_class always contains the latest information.
-
"reltuples", a column in pg_class, stores the estimated number of rows in the table.
-
pg_stats is a table that displays column statistics.
-
Because some of the information accessible through pg_stats includes actual data, it cannot be accessed by regular users.
[Monitoring Performance - Access statistics]
2.10
Select all the INCORRECT statements regarding pg_stat_database.
-
Only one row of operational statistics for the entire database cluster is stored.
-
"blks_hit" stores the number of blocks retrieved from the buffer cache (cache hits).
-
"blks_read" is the sum of the number of blocks read from disk and the number of blocks read from the buffer cache.
-
By default, the "track_counts" parameter is "off", so no activity statistics are collected.
-
"tup_fetched" contains the number of rows fetched using index scans.
[Monitoring Performance - Access statistics]
2.09
Select all the statements that are correct regarding the standard statistics views.
-
From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.
-
From pg_stat_activity, the start time of the current query execution can be obtained.
-
From pg_stat_database, the number of errors that have occurred in the target database can be obtained.
-
From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.
[Monitoring Performance - Access statistics]
2.08
Select all the statements that are correct regarding the standard statistics views.
-
From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.
-
From pg_stat_activity, the start time of the current query execution can be obtained.
-
From pg_stat_database, the number of errors that have occurred in the target database can be obtained.
-
From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.
[Monitoring Performance - Execution plans]
2.07
The EXPLAIN command can be used to display the execution plan of a query.
Select all the SQL commands that are correct as the target of the EXPLAIN command.
-
DELETE
-
DROP TABLE
-
REINDEX
-
PREPARE
-
EXECUTE
[Monitoring Performance - Execution plans]
2.06
Select all the formats that are INCORRECT as valid output options for the EXPLAIN command.
-
JSON
-
HTML
-
CSV
-
YAML
-
XML
[Monitoring Performance - Access statistics]
2.05
Select all the objects that can be targets of locks, which can be confirmed using the pg_locks view.
-
Database
-
Relation
-
Tuple
-
Column
-
Parameter
[Monitoring Performance - Access statistics]
2.04
Select the correct SQL statement to display on table access statistics.
-
SELECT * FROM pg_stat_activity;
-
SELECT * FROM pg_stat_database;
-
SELECT * FROM pg_stat_bgwriter;
-
SELECT * FROM pg_stat_all_tables;
-
SELECT * FROM pg_stat_all_indexes;
[Monitoring Performance - Access statistics]
2.03
Choose two correct statements regarding pg_class.
-
The relpages column stores the number of rows in the table.
-
pg_class stores only information about tables.
-
The latest information is always stored in pg_class.
-
The value in the relpages column is an estimate.
-
Statistics stored in pg_class are updated by certain DDL commands.
[Monitoring Performance - Other performance monitoring]
2.02
Which of the following statements is a correct description of pg_stat_statements?
-
It records execution time spent for SQL statements that exceed a specified threshold.
-
It identifies transactions and target tables currently waiting for locks.
-
It records execution statistics for all executed SQL statements.
-
It displays overall database statistics, with one row per database.
-
It displays table access statistics, with one row per table in the current database.
[Monitoring Performance - Execution plans]
2.01
Select three correct statements regarding the following EXPLAIN output.
postgres=# EXPLAIN SELECT relname,nspname FROM pg_class left join pg_namespace ON (pg_class.relnamespace = pg_namespace.oid);
QUERY PLAN
-------------------------------------------------------------------------
Hash Left Join (cost=1.14..15.97 rows=288 width=128)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
-> Seq Scan on pg_class (cost=0.00..10.88 rows=288 width=68)
-> Hash (cost=1.06..1.06 rows=6 width=68)
-> Seq Scan on pg_namespace (cost=0.00..1.06 rows=6 width=68)
(5 rows)
-
Executing this EXPLAIN command actually executes the SQL statement specified in the argument.
-
The descriptions "Hash Left Join", "Seq Scan on pg_class", and "Seq Scan on pg_namespace" in the plan nodes represent the optimal data processing algorithms chosen automatically by the query planner.
-
The "cost=..." portion represents the actual time required for processing.
-
The "rows=..." portion represents the estimated number of rows that each plan node will process.
-
The "width=..." portion represents the estimated average width (in bytes) of a row based on statistics.
LPI-Japan Platinum Sponsors
© EDUCO (General Incorporated Association Educo) all rights reserved.