E-MapReduce:Query Profile performance diagnostics and optimization use cases

Visualize a Query Profile

StarRocks Manager supports visual analytics for Query Profiles. 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 using CREATE INDEX after you create a table

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

Check 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];

Delete an index

DROP INDEX index_name ON [db_name.]table_name;

Single-column query test

1. Execute a query to filter the s_gender column.

   select * from student_info where s_gender='male';

2. View the Profile.

   Click OLAP_SCAN, select the Node Details tab on the right, and then filter by Bitmap to verify that the Bitmap index is effective.

Create an index

To create an index, specify bloom_filter_columns in PROPERTIES when you create a table. The following is an example.

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

The following are examples:

• 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 for table1.

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

Example test

1. For this example, use the customer table from TPC-H. Add a Bloom filter index to a column with high cardinality 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;

   

3. Execute a query to filter the c_phone column.

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

4. View the Profile.

   Click OLAP_SCAN and then select the Node Details tab on the right. Find the BloomFilterFilterRows metric to confirm that the Bloom filter index is effective.

Query test

1. This example uses the lineitem table from TPC-H. Execute the 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 without a materialized view.

3. View the Profile.

   Click OLAP_SCAN. On the Node tab on the right, you can see that the rollup scans the lineitem table.

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 that a query hits the single-table materialized view

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;

   

2. View the Profile.

   Click OLAP_SCAN. The Node tab on the right shows that the query hits the materialized view, reducing the query time to 0.05 ms.

Create a colocation 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. A table is permanently deleted after it is removed from the recycle bin. By default, a table stays in the recycle bin for one day after you run the DROP TABLE command.

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. This example uses data from TPC-H. 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. Run the following query.

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

3. On the Query Plan tab for a query in EMR StarRocks Manager, you can check whether Colocate Join is in effect.

   If colocate is displayed as true, Colocate Join is effective.