6.10.2. pilotscope.DBController.PostgreSQLController
- class PostgreSQLController(*args, **kwargs)[source]
Bases:
pilotscope.DBController.BaseDBController.BaseDBController- __init__(config: pilotscope.PilotConfig.PostgreSQLConfig, echo=True, enable_simulate_index=False)[source]
- Parameters
config – The config of PilotScope including the config of database.
echo – if true, the more detailed information will be printed when executing the sql statement.
- get_available_extensions()[source]
Get all extensions that have installed in the connected database :return: the list of extension names
- execute(sql, fetch=False, fetch_column_name=False)[source]
Execute a SQL query.
- Parameters
sql – the SQL query to execute
fetch – it indicates whether to fetch the result of the query
fetch_column_name – it indicates whether to fetch the column names of the result.
- Returns
the result of the query if fetch is True, otherwise None
- set_hint(key, value)[source]
Set the value of each hint (i.e., the run-time config) when execute SQL queries. The hints can be used to control the behavior of the database system in a session. For PostgreSQL, you can find all valid hints in https://www.postgresql.org/docs/13/runtime-config.html.
- Parameters
key – the name of the hint
value – the value of the hint
- create_index(index: pilotscope.Common.Index.Index)[source]
Create an index on columns index.columns of table index.table with name index.index_name.
- Parameters
index – a Index object including the information of the index
- drop_index(index: pilotscope.Common.Index.Index)[source]
Drop an index by its index name.
- Parameters
index – an index that will be dropped
- drop_all_indexes()[source]
Drop all indexes across all tables in the database. This will not delete the system indexes and unique indexes.
- get_all_indexes_byte()[source]
Get the size of all indexes across all tables in the database in bytes. This will include the system indexes and unique indexes.
- Returns
the size of all indexes in bytes
- get_table_indexes_byte(table_name)[source]
Get the size of all indexes on a table in bytes. This will include the system indexes and unique indexes.
- Parameters
table_name – a table name that the indexes belong to
- Returns
the size of all indexes on the table in bytes
- get_index_byte(index: pilotscope.Common.Index.Index)[source]
Get the size of an index in bytes by its index name.
- Parameters
index – the index to get size
- Returns
the size of the index in bytes
- get_existed_indexes(table)[source]
Retrieves the existing index on the specified table. This will not include the system indexes and unique indexes.
- Parameters
table – the name of the table
- Returns
a list of pilotscope.common.Index
- get_all_indexes()[source]
Get all indexes across all tables in the database.
- Returns
A collection containing the details of all indexes.
- get_index_number(table)[source]
Get the number of indexes built on the specified table.
- Parameters
table – The name of the table for which to count indexes.
- Returns
The number of indexes on the specified table.
- explain_physical_plan(sql, comment='')[source]
Get the physical plan from database’s optimizer of a SQL query.
- Parameters
sql – The SQL query to be explained.
comment – A SQL comment will be added to the beginning of the SQL query.
- Returns
The physical plan of the SQL query.
- explain_execution_plan(sql, comment='')[source]
Get the execution plan from database’s optimizer of a SQL query.
- Parameters
sql – The SQL query to be explained.
comment – A SQL comment will be added to the beginning of the SQL query.
- Returns
The execution plan of the SQL query.
- get_estimated_cost(sql, comment='')[source]
Get an estimated cost of a SQL query.
- Parameters
sql – The SQL query for which to estimate the cost.
comment – A SQL comment will be added to the beginning of the SQL query.
- Returns
The estimated total cost of executing the SQL query.
- get_explain_sql(sql, execute: bool, comment='')[source]
Constructs an EXPLAIN SQL statement for a given SQL query.
- Parameters
sql – The SQL query to explain.
execute – A boolean flag indicating whether to execute the query plan.
comment – A SQL comment will be added to the beginning of the SQL query.
- Returns
The result of executing the EXPLAIN SQL statement.
- get_buffercache()[source]
Get the numbers of buffer per table in the shared buffer cache in real time.
- Returns
a dict, where keys are the names of table and values are the numbers of buffer per table
- start()[source]
Try to start DBMS. If fails the first time, recover config to self.config.backup_db_config_path and raise DatabaseStartException. If fails again after recovering config, raise DatabaseCrashException.
:raises DatabaseStartException
- is_running()[source]
Check whether the database is running.
- Returns
True if the database is running, False otherwise.
- write_knob_to_file(key_2_value_knob: dict)[source]
Write knobs to config file, you should restart database to make it work.
- Parameters
key_2_value_knob – a dict with keys as the names of the knobs and values as the values to be set.
- recover_config()[source]
Recover config file of database to the lasted saved config file by backup_config()
- get_table_columns(table_name, enable_all_schema=False)[source]
Retrieves all column names for a given table. If enable_all_schema is true, Pilotscope will search it across all schemas in the database. Otherwise, Pilotscope will only search it in the public schema.
- Parameters
table_name – The name of the table for which to retrieve column names.
enable_all_schema –
- Returns
A list of column names for the specified table.
- get_number_of_distinct_value(table_name, column_name)[source]
Get the number of distinct value of a column
- Parameters
table_name – the name of the table that the column belongs to
column_name – the name of the column
- Returns
the number of distinct value, type of which is same as the data of the column
- class SimulateIndexVisitor(db_controller: pilotscope.DBController.PostgreSQLController.PostgreSQLController)[source]
Bases:
object- __init__(db_controller: pilotscope.DBController.PostgreSQLController.PostgreSQLController)[source]
Initialize self. See help(type(self)) for accurate signature.