Use Index Scan on AO Tables

SynxDB supports IndexScan on Append-Optimized (AO) tables to improve query efficiency in certain scenarios, such as the following query:

SELECT * FROM tbl WHERE val > 100 ORDER BY val LIMIT 10;

Tip

An Append-Optimized (AO) table is an optimized storage method oriented towards batch-insert-heavy scenarios, such as big data analytics and data warehousing.

When new data is inserted into an AO table, SynxDB appends the new data to the end of the table instead of finding free space for insertion as it would with a regular table. This means that inserting data into an AO table only requires appending to the file, which results in higher insertion efficiency.

For the query statement above:

  • If using heap table storage, SynxDB can execute this query by finding 10 tuples where val is greater than 100 through an IndexScan. It only needs to read about 10 tuples from the index and the data table.

  • If using AO table storage, and assuming the tbl table has 1 billion rows, while we only need to return 10 tuples specified by the LIMIT clause:

    SynxDB supports using the IndexScan operation to scan AO tables. This can substantially reduce the amount of data scanned and significantly improve scanning efficiency, making it a better scanning method than SeqScan and BitmapScan.

Use cases

This feature is suitable for querying large tables while using ORDER BY and LIMIT clauses to restrict the size of the returned result set:

SELECT * FROM tbl WHERE val > 100 ORDER BY val LIMIT 10;

Note

“A large table” can be understood as a table that is about 3000 times the size of the data to be queried. For example, when using LIMIT 10 to return 10 records, a table with more than 30,000 records can be considered large.

The degree of optimization might vary depending on the amount of data. In the most suitable scenarios, a query that originally took tens of minutes can return in under a second.

Usage example

To enable AO IndexScan, you first need to disable the GPORCA optimizer by running SET optimizer TO off; and then set the system parameter gp_enable_ao_indexscan to ON.

AO IndexScan relies on an index over the queried columns, so besides setting the two parameters above, you also need an index on the columns used for filtering and ordering.

Prepare an AOCO table with 100 million rows and create an index on the val column:

SET optimizer TO off; -- To enable AO IndexScan, you need to disable the ORCA optimizer first

CREATE TABLE tbl (id int, val int) WITH (orientation='column', appendonly=true);
INSERT INTO tbl SELECT i, i FROM generate_series(1, 100000000) s(i);
CREATE INDEX ON tbl(val);
ANALYZE tbl;

While gp_enable_ao_indexscan keeps its default value off, the query still scans the whole table and then sorts, even though the index exists:

EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM tbl WHERE val > 100 ORDER BY val LIMIT 10;

 Limit (actual rows=10 loops=1)
   ->  Gather Motion 4:1  (slice1; segments: 4) (actual rows=10 loops=1)
         ->  Limit (actual rows=10 loops=1)
               ->  Sort (actual rows=10 loops=1)
                     Sort Key: val
                     Sort Method:  top-N heapsort  Memory: 100kB
                     ->  Seq Scan on tbl (actual rows=25003597 loops=1)
 Optimizer: Postgres query optimizer
 Execution Time: 3102.336 ms

Once it is enabled, the same query switches to an index scan and reads only the first 10 qualifying rows:

SET gp_enable_ao_indexscan TO on;
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM tbl WHERE val > 100 ORDER BY val LIMIT 10;

 Limit (actual rows=10 loops=1)
   ->  Gather Motion 4:1  (slice1; segments: 4) (actual rows=10 loops=1)
         ->  Limit (actual rows=10 loops=1)
               ->  Index Scan using tbl_val_idx on tbl (actual rows=10 loops=1)
 Optimizer: Postgres query optimizer
 Execution Time: 0.800 ms

The Seq Scan reads 25 million rows on a single segment, whereas the Index Scan produces only 10 rows, and the execution time drops from 3102 ms to 0.8 ms.

gp_enable_ao_indexscan also controls index-only scans

When every column the query uses is covered by the index, enabling gp_enable_ao_indexscan produces an Index Only Scan in the plan:

EXPLAIN (COSTS OFF) SELECT val FROM tbl WHERE val > 100 ORDER BY val LIMIT 10;

 Limit
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Limit
               ->  Index Only Scan using tbl_val_idx on tbl
                     Index Cond: (val > 100)
 Optimizer: Postgres query optimizer

While the parameter is off, neither Index Scan nor Index Only Scan is used for AO tables. Even with enable_seqscan manually turned off, the plan only falls back to a bitmap scan. Bitmap scans are not governed by this parameter and remain available in both states. Index scans on heap tables are likewise unaffected by it.

Currently, this feature is only applicable to the PostgreSQL optimizer. Also, because random I/O can be up to 30,000 times less efficient than sequential I/O on some mechanical hard drives, the execution efficiency of IndexScan might not be high if this feature is enabled on underperforming mechanical hard drives.