AnalyticDB:Best practices for performing operations on OSS foreign tables

View execution plans

AnalyticDB for PostgreSQL allows you to view execution plans of query statements that involve OSS foreign tables by executing an EXPLAIN statement. Example:

EXPLAIN SELECT COUNT(*) FROM oss_lineitem_orc WHERE l_orderkey > 14062498;

Collect statistics

By default, AnalyticDB for PostgreSQL does not automatically collect statistics of OSS foreign tables because these tables are stored in OSS. If statistics are unavailable or out-of-date, the query optimizer may generate inefficient query plans for complex queries such as those on joined tables. To solve this problem, AnalyticDB for PostgreSQL allows you to update the statistics of OSS foreign tables by executing an ANALYZE statement.

1. Execute the following EXPLAIN statement to view the current execution plan:

   EXPLAIN <table_name>;

2. Execute the following ANALYZE statement to collect statistics:

   ANALYZE <table_name>;

3. Execute the following EXPLAIN statement to view the new execution plan:

   EXPLAIN <table_name>;

Split large objects

OSS foreign tables allow you to scan source objects that are stored on different nodes in parallel. If a small number of source objects exist, we recommend that you split these objects into small objects to facilitate parallel scans on different nodes.

The following example demonstrates how to split a large object. In this example, the Linux operating system is used.

1. Query the number of rows in the current large object.

   wc -l <csv_file>

2. Split the object into small objects based on the value of the N parameter. N specifies the number of rows in each small object.

   split -l N <csv_file>

   Note

   A row in an object cannot be split into different small objects. The preceding splitting method ensures the integrity of each row.

View the object information of OSS foreign tables

You can execute the following statement to query the object information of an OSS foreign table:

SELECT * FROM get_oss_table_meta('<OSS FOREIGN TABLE>');

References

• Use OSS foreign tables for data lake analysis

• Use partitioned OSS foreign tables

• Use OSS foreign tables to import and export data

• Data type mappings for OSS foreign tables