Query Profile performance diagnostics and optimization case studies

Visualize a Query Profile

You can use StarRocks Manager to visually analyze a Query Profile. For more information, see Query Profile introduction.

Create an index

• Create a Bitmap index when you create a table.

  CREATE TABLE `student_info` (
    `s_stukey` bigint(20) NULL COMMENT "",
    `s_name` varchar(65533) NULL COMMENT "",
    `s_gender` varchar(65533) NULL COMMENT "",
    INDEX index1 (s_gender) USING BITMAP COMMENT 'index1'
  ) ENGINE=OLAP
  DUPLICATE KEY(`s_stukey`)
  COMMENT "OLAP"
  DISTRIBUTED BY HASH(`s_stukey`);
  INSERT INTO student_info
  VALUES
      (001,'student#000000019','male'),
      (002,'student#000000020','male'),
      (003,'student#000000021','male'),
      (004,'student#000000022','female');

• Create a Bitmap index on an existing table by using CREATE INDEX.

  CREATE INDEX index_name ON table_name (column_name) [USING BITMAP] [COMMENT ''];

Check the index creation progress

SHOW ALTER TABLE COLUMN [FROM db_name];

View an index

SHOW {INDEX[ES] | KEY[S] } FROM [db_name.]table_name [FROM db_name];

MySQL [db_test]> show index from student_info;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| db_test.student_info   |            | index1   |              | s_gender    |           |             |          |        |      | BITMAP     | index1  |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

Drop an index

DROP INDEX index_name ON [db_name.]table_name;

Test a single-column query

1. Execute a query that filters the s_gender column.

   select * from student_info where s_gender='male';

2. View the Profile.

   Click OLAP_SCAN, then click the Node Details tab on the right. Filter the metrics for Bitmap to confirm that the Bitmap index has taken effect.

Create an index

When you create a table, you can create a Bloom filter index by specifying bloom_filter_columns in the PROPERTIES clause. The following example shows how to do this.

CREATE TABLE table1
(
  k1 BIGINT,
  k2 LARGEINT,
  v1 VARCHAR(2048) REPLACE,
  v2 SMALLINT DEFAULT "10"
)
ENGINE = olap
PRIMARY KEY(k1, k2)
DISTRIBUTED BY HASH (k1, k2) BUCKETS 10
PROPERTIES("bloom_filter_columns" = "k1,k2"); -- Separate multiple index columns with commas (,).

View an index

SHOW CREATE TABLE table1;

Modify an index

For example:

• Add a Bloom filter index for column v1.

  ALTER TABLE table1 SET ("bloom_filter_columns" = "k1,k2,v1");

• Remove the Bloom filter index for column k2.

  ALTER TABLE table1 SET ("bloom_filter_columns" = "k1");

• Delete all Bloom filter indexes from table1.

  ALTER TABLE table1 SET ("bloom_filter_columns" = "");

Example

1. Take the customer table from TPC-H as an example. Add a Bloom filter index on a high-cardinality column that is not a sort key, such as the c_phone column.

   ALTER TABLE tpc_h_sf100.customer SET ("bloom_filter_columns" = "c_custkey, c_phone");

2. View the index to confirm that the Bloom filter index has been added.

   SHOW CREATE TABLE tpc_h_sf100.customer;

   | customer | CREATE TABLE `customer` (
     `c_custkey` bigint(20) NULL COMMENT "",
     `c_name` varchar(65533) NULL COMMENT "",
     `c_address` varchar(65533) NULL COMMENT "",
     `c_nationkey` bigint(20) NULL COMMENT "",
     `c_phone` varchar(65533) NULL COMMENT "",
     `c_acctbal` double NULL COMMENT "",
     `c_mktsegment` varchar(65533) NULL COMMENT "",
     `c_comment` varchar(65533) NULL COMMENT "",
     `Gender` varchar(65533) NULL DEFAULT "default_value" COMMENT ""
   ) ENGINE=OLAP
   DUPLICATE KEY(`c_custkey`)
   COMMENT "OLAP"
   DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24
   PROPERTIES (
   "replication_num" = "1",
   "bloom_filter_columns" = "c_custkey, c_phone",
   "in_memory" = "false",
   "storage_format" = "DEFAULT",
   "enable_persistent_index" = "false",
   "compression" = "LZ4"
   ); |

3. Execute a query that filters the c_phone column.

   select * from tpc_h_sf100.customer where c_phone = "10-334-921-5346";

4. View the Profile.

   Click OLAP_SCAN, then click the Node Details tab on the right. Find the BloomFilterFilterRows metric to confirm that the Bloom filter index is active.

Test a query

1. Take the lineitem table from TPC-H as an example and execute a query.

   select l_returnflag,l_linestatus,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

2. The initial query takes 1115 ms to complete because no materialized view has been created.

3. View the Profile.

   Click OLAP_SCAN and go to the Node tab on the right. You can see that the Rollup scanned the lineitem table itself.

Create a materialized view

CREATE MATERIALIZED VIEW material_test AS select l_returnflag,l_linestatus ,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

Verify materialized view hit

1. You can use the EXPLAIN command to check if the query hits the single-table materialized view.

   explain select l_returnflag,l_linestatus ,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

   In the returned result, rollup: material_test indicates that the query hit the materialized view named material_test.

   0:OlapScanNode
        TABLE: lineitem
        PREAGGREGATION: OFF. Reason: null
        partitions=1/1
        rollup: material_test
        tabletRatio=96/96
        tabletList=15646,15648,15650,15652,15654,15656,15658,15660,15662,15664 ...
        cardinality=600037902
        avgRowSize=14.285666
        numNodes=0

2. View the Profile.

   Click OLAP_SCAN. On the Node tab on the right, you can see that the query hits the materialized view and the query time is reduced to 0.05 ms.

Create a colocated table

CREATE TABLE tbl (k1 int, v1 int sum)
DISTRIBUTED BY HASH(k1)
BUCKETS 8
PROPERTIES(
    "colocate_with" = "group1"
);

Delete a Colocation Group

When the last table in a Group is permanently deleted, the Group is also automatically deleted. Permanent deletion means that the table is removed from the Recycle Bin. Typically, after a table is deleted by using the DROP TABLE command, it remains in the Recycle Bin for one day by default before it is permanently deleted.

View group information

SHOW PROC '/colocation_group';

+-------------+--------------+----------+------------+----------------+----------+----------+
| GroupId     | GroupName    | TableIds | BucketsNum | ReplicationNum | DistCols | IsStable |
+-------------+--------------+----------+------------+----------------+----------+----------+
| 11912.11916 | 11912_group1 | 11914    | 8          | 3              | int(11)  | true     |
+-------------+--------------+----------+------------+----------------+----------+----------+

SHOW PROC '/colocation_group/GroupId';
SHOW PROC '/colocation_group/11912.11916';

+-------------+---------------------+
| BucketIndex | BackendIds          |
+-------------+---------------------+
| 0           | 10002, 10004, 10003 |
| 1           | 10002, 10004, 10003 |
| 2           | 10002, 10004, 10003 |
| 3           | 10002, 10004, 10003 |
| 4           | 10002, 10004, 10003 |
| 5           | 10002, 10004, 10003 |
| 6           | 10002, 10004, 10003 |
| 7           | 10002, 10004, 10003 |
+-------------+---------------------+
8 rows in set (0.00 sec)

Modify group properties

ALTER TABLE tbl SET ("colocate_with" = "group_name");

1. Using the TPC-H data as an example, perform the following operations.

   use tpc_h_sf100;
   ALTER TABLE orders SET ("colocate_with" = "cg_tpc_orders");
   ALTER TABLE lineitem SET ("colocate_with" = "cg_tpc_orders");

2. Execute the following query.

   select count(1) from orders as o join lineitem as l on o.o_orderkey = l.l_orderkey;

3. You can check whether the Colocate Join is active on the Query Plan tab for the query in the StarRocks Manager UI.

   When colocate is true, it indicates that Colocate Join has taken effect. This is indicated by colocate: true in the execution plan. The corresponding execution plan fragment is:

   2:HASH JOIN
   |  join op: INNER JOIN (COLOCATE)
   |  colocate: true
   |  equal join conjunct: 10: l_orderkey = 1: o_orderkey
   |
   |----1:OlapScanNode
   |       TABLE: orders
   |       PREAGGREGATION: ON
   |       PREDICATES: 1: o_orderkey IS NOT NULL
   |       partitions=1/1
   |       rollup: orders
   |       tabletRatio=96/96
   |       tabletList=12403,12405,12407,12409,12411,12413,12415,12417,12419,12421 ...
   |       cardinality=135000000
   |       avgRowSize=1.0
   |       numNodes=0