First, we have successfully installed the KES database locally. To ensure smooth execution of subsequent operations, we need to prepare some basic tables and data. The commands are as follows:

create table t1(id int, val int, name varchar(64));
create table t2(id int, val int);
create index t1_idx on t1(id);
create index t2_idx on t2(id);
insert into t1 select i, i%5000, CONCAT('Kingbase',(i%5)) from generate_series(1,3000000) as x(i);
insert into t2 select i, i%5000 from generate_series(1,1000000) as x(i);

To prevent interference from parallel processing, all examples below are executed with parallel scan disabled:

set max_parallel_workers_per_gather = 0;

Execution Plan Analysis

As developers, we frequently interact with execution plans. While our SQL queries may function correctly, their performance can significantly impact application efficiency. Before deploying queries to production, it's crucial to analyze the execution plan to ensure optimal performance.

In KingbaseES, the EXPLAIN command is used to view execution plans. Its syntax is:

explain [option] statement

The option can be one or more of the following:

• analyze: Executes the query and shows runtime statistics (default false)
• verbose: Displays additional information including field names in the plan tree (default false)
• costs: Shows cost estimates (default true)
• buffers: Shows buffer usage information (requires analyze)
• format: Output format (TEXT, XML, JSON, YAML, default TEXT)

Example analysis:

explain analyze select * from t1 t where id = 555;

• Operation: Index Scan using t1_idx on t1
  • Index Scan indicates index scanning, Seq Scan indicates full table scan
  • t1_idx is the index name
• Estimated Cost: (cost=0.43..8.45 rows=1 width=18)
  • 0.43: startup cost for first row
  • 8.45: total cost for all rows
• Actual Cost: (actual time=0.039..0.040 rows=1 loops=1)
  • Only appears with analyze
  • rows=1: actual returned rows
  • loops=1: number of times operatino executed
• Index Condition: Index Cond: (id = 555)
  • Condition that triggered index usage

Index Optimization

Expression Indexes

When functions are applied to columns in WHERE clauses, regular indexes are typically ineffective. However, expression indexes can help:

create index idx_t1 on t1(upper(name));

This ensures case-insensitive searches work efficiently.

LIKE Pattern Matching

For suffix matching patterns like '%2', full table scans occur. To optimize:

create index on t1(reverse(name) collate "C");
explain select * from t1 where reverse(t1.name) like '2%';

The transformation LIKE '%abc' becomes LIKE 'cba%' via reverse function, enabling index usage.

Partial Indexes

Create indexes on subsets of data for frequently accessed portions:

create index idx_t2 on t2(id) where id < 500;

Using Hints

Force specific index usage when needed:

explain select * from t1 where id=20;
set enable_hint = TRUE;
explain select/*+seqscan(t1)*/ * from t1 where id=20;

Removing Unnecessary Indexes

Identify unused indexes:

select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from sys_stat_user_indexes order by idx_scan, idx_tup_read, idx_tup_fetch;

Maintenance Tasks

Regularly vacuum and rebuild indexes to maintain performance:

• Database level: REINDEX DATABASE d1;
• Table level: REINDEX TABLE t1;
• Index level: REINDEX INDEX idx1;

Rebuilding indexes reduces file size and improves query speed, especially in environments with frequent updates.

Summary

This article explored SQL optimization techniques in the KES database, focusing on execution plan analysis and index optimization. Understanding these concepts allows developers to enhance query efficiency and avoid performance bottlenecks. By leveraging specialized index types like expression indexes, reverse indexes, and partial indexes, complex queries can be optimized effectively. Regular maintenance through vacuuming and index rebuilding ensures sustained database performance.