All Products
Search
Document Center

AnalyticDB:Best practices for performing operations on OSS foreign tables

Last Updated:Aug 29, 2024

This topic describes how to perform operations on Object Storage Service (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