Try PolarDB PostgreSQL columnstore index for free

Background

PolarDB columnstore index is a query acceleration solution developed for hybrid transactional and analytical processing (HTAP) scenarios and slow SQL statement scenarios in online transactional processing (OLTP). Typical scenarios include real-time statistics and analysis, flexible queries where composite indexes are inefficient, spatiotemporal statistics based on geographic grids, and accelerated statistics for cold data combined with OSS. The advantages include the following:

• High performance: Performance can be 15 to 30 times faster, and up to 100 times faster, than the native PostgreSQL row store execution engine. For example, in TPC-H scenarios, performance is nearly 60 times faster.

• Low cost: Create columnstore indexes only for the columns used in queries. You do not need to convert the entire table to a column store format. Columnstore indexes require less storage space. In addition, PolarDB for PostgreSQL supports queries and analysis of cold data in OSS, which can effectively reduce data storage costs.

• Ease of use: The feature is compatible with PostgreSQL syntax and supports the PostgreSQL indexing mechanism. It also supports fine-grained control over OLTP and online analytical processing (OLAP) queries.

• Real-time consistency: The feature automatically maintains real-time data consistency between the row store and the column store. You do not need to build a separate data transformation pipeline.

• Comprehensive features:

  • Supports Prepared Statement syntax.

  • Supports partitioned tables and pg_pathman.

  • Accelerates SELECT statements within transaction blocks.

  • Supports extract, transform, and load (ETL) features such as CREATE TABLE AS.

Alibaba Cloud provides the Business Foundation System Solutions Experience Center. This center offers a real, free PolarDB cluster environment and out-of-the-box testing methods. You can quickly experience the performance improvements provided by the columnstore index online.

Impact

This free trial does not involve your production environment and will not affect your business.

Costs

The resources used in this trial are not part of your account, and no fees will be incurred. You can proceed with the trial without concern.

Trial content

Procedure

1. Go to the Business Foundation System Solutions Experience Center to try the Columnstore Index/Table - Accelerate Complex TP SQL Queries trial for free.

2. In the product options, select PolarDB PostgreSQL.

3. Click the Create Free Trial Task button at the bottom of the page.

4. In the Confirm Creation of Query Acceleration Trial Task dialog box, click OK.

5. After a moment, your trial task appears.

6. Click View Details to go to the real-time query experience page.

7. Run SQL queries for two scenarios: single-table statistics and multi-table JOINs. Use both the row store engine and the column store engine.

   Note

   • Follow the on-screen prompts and click the buttons to perform each step. If you do not click a button before the countdown ends, the operation is performed automatically.

   • During the trial, you can observe the CPU utilization in the trend chart at the bottom.

   1. Click Start Task.

   2. Click Select Database. The system automatically runs the following command to switch to the target database.

      \c mydb

   3. First, run a query for the single-table statistics scenario.

      1. First, run a standard query for single-table statistics. Click Disable Vectorized Engine. The system automatically runs the following command.

         set polar_csi.enable_query to off;

      2. Click View SQL Execution Plan. The system automatically runs the following command to view the execution plan for the SQL statement.

         explain select
            l_returnflag,
            l_linestatus,
            sum(l_quantity) as sum_qty,
            sum(l_extendedprice) as sum_base_price,
            sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
            sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
            avg(l_quantity) as avg_qty,
            avg(l_extendedprice) as avg_price,
            avg(l_discount) as avg_disc,
            count(*) as count_order
         from
            lineitem
         where l_shipdate <= date '1998-12-01' - '60 day'::interval
          group by
            l_returnflag,
            l_linestatus
         order by
            l_returnflag,
            l_linestatus;

         The query plan does not use the columnstore index. The following result is returned:

      3. Click Execute SQL. The system automatically runs the following SQL statement:

         select
            l_returnflag,
            l_linestatus,
            sum(l_quantity) as sum_qty,
            sum(l_extendedprice) as sum_base_price,
            sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
            sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
            avg(l_quantity) as avg_qty,
            avg(l_extendedprice) as avg_price,
            avg(l_discount) as avg_disc,
            count(*) as count_order
         from
            lineitem
         where l_shipdate <= date '1998-12-01' - '60 day'::interval
          group by
            l_returnflag,
            l_linestatus
         order by
            l_returnflag,
            l_linestatus;

         The following result is returned. The running time is 21.034 seconds, and the CPU utilization increases significantly.

      4. Next, run an accelerated query for single-table statistics. Click Enable Vectorized Engine. The system automatically runs the following command.

         set polar_csi.enable_query to on;

      5. Click View SQL Execution Plan. The system automatically runs the following command to view the execution plan for the SQL statement.

         explain select
            l_returnflag,
            l_linestatus,
            sum(l_quantity) as sum_qty,
            sum(l_extendedprice) as sum_base_price,
            sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
            sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
            avg(l_quantity) as avg_qty,
            avg(l_extendedprice) as avg_price,
            avg(l_discount) as avg_disc,
            count(*) as count_order
         from
            lineitem
         where l_shipdate <= date '1998-12-01' - '60 day'::interval
          group by
            l_returnflag,
            l_linestatus
         order by
            l_returnflag,
            l_linestatus;

         The query plan now uses the columnstore index. The following result is returned:

      6. Click Execute SQL. The system automatically runs the following SQL statement:

         select
            l_returnflag,
            l_linestatus,
            sum(l_quantity) as sum_qty,
            sum(l_extendedprice) as sum_base_price,
            sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
            sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
            avg(l_quantity) as avg_qty,
            avg(l_extendedprice) as avg_price,
            avg(l_discount) as avg_disc,
            count(*) as count_order
         from
            lineitem
         where l_shipdate <= date '1998-12-01' - '60 day'::interval
          group by
            l_returnflag,
            l_linestatus
         order by
            l_returnflag,
            l_linestatus;

         The following result is returned. The running time is 0.444 seconds, and there is no significant change in CPU utilization.

   4. Start the query for the multi-table JOIN scenario.

      1. First, run a standard query for the multi-table JOIN. Click Disable Vectorized Engine. The system automatically runs the following command.

         set polar_csi.enable_query to off;

      2. Click View SQL Execution Plan. The system automatically runs the following command to view the execution plan for the SQL statement.

         explain select
            l_orderkey,
            sum(l_extendedprice * (1 - l_discount)) as revenue,
            o_orderdate,
            o_shippriority
         from
            customer,orders,
            lineitem
         where
            c_mktsegment = 'FURNITURE'
            and c_custkey = o_custkey
            and l_orderkey = o_orderkey
            and o_orderdate < date '1995-03-05'
            and l_shipdate > date '1995-03-05'
         group by
            l_orderkey,
            o_orderdate,
            o_shippriority
         order by revenue desc, o_orderdate limit 10;

         The query plan does not use the columnstore index. The following result is returned:

      3. Click Execute SQL. The system automatically runs the following SQL statement:

         select
            l_orderkey,
            sum(l_extendedprice * (1 - l_discount)) as revenue,
            o_orderdate,
            o_shippriority
         from
            customer,orders,
            lineitem
         where
            c_mktsegment = 'FURNITURE'
            and c_custkey = o_custkey
            and l_orderkey = o_orderkey
            and o_orderdate < date '1995-03-05'
            and l_shipdate > date '1995-03-05'
         group by
            l_orderkey,
            o_orderdate,
            o_shippriority
         order by revenue desc, o_orderdate limit 10;

         The following result is returned. The running time is 6.789 seconds, and the CPU utilization increases significantly.

         Note

         To observe CPU utilization, you can click Standard Query or Vectorized Engine below the chart to compare the CPU utilization between the two.

         

      4. Then, click Enable Vectorized Engine to run an accelerated query on a single table for statistics. The system automatically executes the following command.

         set polar_csi.enable_query to on;

      5. Click View SQL Execution Plan. The system automatically runs the following command to view the execution plan for the SQL statement.

         explain select
            l_orderkey,
            sum(l_extendedprice * (1 - l_discount)) as revenue,
            o_orderdate,
            o_shippriority
         from
            customer,orders,
            lineitem
         where
            c_mktsegment = 'FURNITURE'
            and c_custkey = o_custkey
            and l_orderkey = o_orderkey
            and o_orderdate < date '1995-03-05'
            and l_shipdate > date '1995-03-05'
         group by
            l_orderkey,
            o_orderdate,
            o_shippriority
         order by revenue desc, o_orderdate limit 10;

         The query plan now uses the columnstore index. The following result is returned:

      6. Click Execute SQL. The system automatically runs the following SQL statement:

         select
            l_orderkey,
            sum(l_extendedprice * (1 - l_discount)) as revenue,
            o_orderdate,
            o_shippriority
         from
            customer,orders,
            lineitem
         where
            c_mktsegment = 'FURNITURE'
            and c_custkey = o_custkey
            and l_orderkey = o_orderkey
            and o_orderdate < date '1995-03-05'
            and l_shipdate > date '1995-03-05'
         group by
            l_orderkey,
            o_orderdate,
            o_shippriority
         order by revenue desc, o_orderdate limit 10;

         The following result is returned. The running time is 0.325 seconds, and there is no significant change in CPU utilization.

         Note

         To observe CPU utilization, you can click Standard Query or Vectorized Engine below the chart to compare the CPU utilization between the two.

         

8. While the SQL statements are running, you can observe the changes in the average CPU utilization of the cluster in the trend chart on the left.

   Note

   Real-time monitoring data may have a delay. To ensure that the complete CPU change is displayed, the trend chart automatically extends the monitoring time by 2 to 3 seconds after the SQL statement finishes.

9. (Optional) To view the results of created tasks, go to the Columnstore Index - Accelerate Complex TP SQL Queries page and click Trial Records. In the task list, click All Tasks or My Tasks to view the trial results and their details.

Result analysis

This trial demonstrates the following advantages of the PolarDB for PostgreSQL columnstore index:

1. Simple to use. You can achieve acceleration without changing your SQL queries.

2. For single-table statistics and multi-table JOIN scenarios, the columnstore index improves performance by 30 times or more.

Columnstore index significantly accelerates queries

• Running time: After the columnstore index is enabled, the running time for complex queries is greatly reduced. The single-table statistics query time drops from 29.998 seconds to 0.496 seconds. The multi-table JOIN query time drops from 15.105 seconds to 0.354 seconds.

• Execution plan: After the columnstore index is enabled, the execution plan includes the CSI Executor. This indicates that the columnstore index feature is used.

Columnstore index processes queries more efficiently

The running time and corresponding CPU utilization show that the columnstore index achieves faster queries with lower CPU utilization.

Single-table statistics query

Multi-table JOIN query

Related topics

• In-Memory Column Index (IMCI)

• Enable and use a columnstore index

Contact experts

You can join the official DingTalk group for technical support and consultations. The DingTalk group number is 75850003226.