O In-Memory Column Index (IMCI) da Enterprise Edition do PolarDB for PostgreSQL oferece desempenho de consulta mais de 50 vezes superior ao mecanismo row store e reduz o armazenamento em até 80%.
Este tópico apresenta um benchmark TPC-H com fator de escala de 100 GB, cobrindo preparação de dados, criação de índices, execução de consultas e resultados. Crie um IMCI com o tipo de índice CSI: CREATE INDEX ... USING csi(...).
A implementação do TPC-H neste tópico baseia-se nos benchmarks do TPC-H. Estes resultados não são comparáveis aos resultados oficiais publicados, pois os testes não atendem integralmente a todos os requisitos do TPC-H.
Descrição do teste
O benchmark utiliza o seguinte ambiente. A instância ECS gera os dados e envia as consultas ao cluster PolarDB for PostgreSQL.
|
Componente |
Especificação |
|
Instância ECS |
16 núcleos, 32 GB ( |
|
Cluster PolarDB for PostgreSQL |
32 núcleos, 256 GB. Mecanismo de banco de dados: PostgreSQL 14 com versão secundária do mecanismo 14.10.20.0 ou posterior. Edição: Enterprise Edition. Série: Dedicated. |
Certifique-se de que a instância ECS e o cluster PolarDB estejam na mesma virtual private cloud (VPC).
Metodologia do benchmark
O TPC-H é um benchmark de suporte à decisão publicado pelo Transaction Processing Performance Council (TPC). Ele avalia o desempenho analítico do banco de dados usando 8 tabelas e 22 consultas SQL complexas que abrangem estatísticas de tabela única, junções de múltiplas tabelas, subconsultas, agregações e ordenação.
Este benchmark usa um fator de escala de 100 GB. Todas as 22 consultas são executadas com EXPLAIN ANALYZE para capturar planos de execução e tempos, permitindo comparar o IMCI com o mecanismo row store padrão.
Preparação dos dados
Etapa 1: Criar o banco de dados
No cluster PolarDB for PostgreSQL, crie uma conta privilegiada e crie o banco de dados de teste tpchdb.
Etapa 2: Gerar dados do TPC-H
Faça login na instância ECS e prepare os dados de teste.
-
Baixe a ferramenta TPC-H dbgen.tar.gz, faça upload para a instância ECS e, em seguida, descompacte e compile o arquivo.
--- Decompress the package tar -zxvf dbgen.tar.gz --- Compile the code cd ./dbgen make -f makefile.suite -
Use a ferramenta TPC-H para gerar 100 GB de dados de teste. Esta operação leva cerca de 30 minutos.
--- Change the directory to dbgen ./dbgen -s 100 -f
Etapa 3: Importar dados para o PolarDB
Na instância ECS, use a ferramenta de conexão do PolarDB (PolarDB-Tools) para conectar-se ao seu cluster e importar os dados de teste para o banco de dados tpchdb.
-
Confirme se você está no diretório
dbgen. A saída esperada é/<installation_path>/dbgen.--- Confirm the current directory pwd -
Use o PolarDB-Tools para conectar-se ao cluster via linha de comando.
/u01/polardb_pg_tools/bin/psql -h <cluster_endpoint> -p <port_for_cluster_endpoint> -U <privileged_account> -d tpchdb -
Importe os dados de teste.
\i ./dss.ddl \copy part from ./part.tbl with delimiter as '|' NULL ''; \copy region from ./region.tbl with delimiter as '|' NULL ''; \copy nation from ./nation.tbl with delimiter as '|' NULL ''; \copy orders from ./orders.tbl with delimiter as '|' NULL ''; \copy customer from ./customer.tbl with delimiter as '|' NULL ''; \copy lineitem from ./lineitem.tbl with delimiter as '|' NULL ''; \copy partsupp from ./partsupp.tbl with delimiter as '|' NULL ''; \copy supplier from ./supplier.tbl with delimiter as '|' NULL '';
Ativar o IMCI e criar índices columnstore
Etapa 1: Ativar o IMCI
De acordo com a versão do seu cluster PolarDB for PostgreSQL, ative o recurso de índice columnstore.
Etapa 2: Criar índices em todas as tabelas de teste
Crie um IMCI em cada uma das 8 tabelas de teste. O SQL a seguir adiciona restrições de chave primária (obrigatórias para o IMCI) e cria índices columnstore usando o tipo de índice csi.
--partsupp table
ALTER TABLE PARTSUPP ADD CONSTRAINT partsupp_pkey PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);
CREATE INDEX imps ON partsupp USING csi(ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment);
--part table
ALTER TABLE PART ADD CONSTRAINT part_kpey PRIMARY KEY (P_PARTKEY);
CREATE INDEX im_p ON part USING csi(p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment);
--supplier table
ALTER TABLE SUPPLIER ADD CONSTRAINT supplier_pkey PRIMARY KEY (S_SUPPKEY);
CREATE INDEX im_s ON supplier USING csi(s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment);
--customer table
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_pkey PRIMARY KEY (C_CUSTKEY);
CREATE INDEX im_c ON customer USING csi(c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment);
--orders table
ALTER TABLE ORDERS ADD CONSTRAINT orders_pkey PRIMARY KEY (O_ORDERKEY);
CREATE INDEX im_o ON orders USING csi(o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment);
--lineitem table
ALTER TABLE LINEITEM ADD CONSTRAINT lineitem_pkey PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);
CREATE INDEX im_l ON lineitem USING csi(l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment);
--nation table
ALTER TABLE NATION ADD CONSTRAINT nation_pkey PRIMARY KEY (N_NATIONKEY);
CREATE INDEX im_n ON nation USING csi(n_nationkey, n_name, n_regionkey, n_comment);
--region table
ALTER TABLE REGION ADD CONSTRAINT region_pkey PRIMARY KEY (R_REGIONKEY);
CREATE INDEX im_r ON region USING csi(r_regionkey, r_name, r_comment);Etapa 3: Configurar parâmetros de consulta do IMCI
Defina os seguintes parâmetros antes de executar as consultas do benchmark. Cada parâmetro está descrito em Parâmetros.
SET polar_csi.enable_pk TO ON;
SET polar_csi.enable_query TO ON;
SET polar_csi.cost_threshold TO 0;
SET polar_csi.memory_limit TO 65536;
// High kernel version
SET polar_csi.max_parallel_workers TO 32;
// Low kernel version
SET polar_csi.exec_parallel TO 32;O parâmetro polar_csi.max_parallel_workers chamava-se anteriormente polar_csi.exec_parallel em versões anteriores do kernel. Para versões do kernel que não suportam polar_csi.max_parallel_workers, use polar_csi.exec_parallel em seu lugar.
-
PostgreSQL 14:
Use
polar_csi.exec_parallelnas versões 2.0.14.20.42.0 e anteriores.Use
polar_csi.max_parallel_workersnas versões 2.0.14.20.43.0 e posteriores.
-
PostgreSQL 16:
Use
polar_csi.exec_parallelnas versões 2.0.16.11.15.0 e anteriores.Use
polar_csi.max_parallel_workersnas versões 2.0.16.13.16.0 e posteriores.
Executar consultas do benchmark
Execute todas as 22 consultas TPC-H no seu cluster PolarDB for PostgreSQL e registre os tempos de execução.
-
Ative o cronômetro para capturar o tempo total de execução de cada instrução SQL.
\timing Execute as seguintes instruções SQL em ordem.
Q1
EXPLAIN ANALYZE
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;Q2
EXPLAIN ANALYZE
SELECT
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
FROM
part,
supplier,
partsupp,
nation,
region
WHERE
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 43
and p_type like '%NICKEL'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and ps_supplycost = (
SELECT
min(ps_supplycost)
FROM
partsupp,
supplier,
nation,
region
WHERE
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
)
ORDER BY
s_acctbal desc,
n_name,
s_name,
p_partkey limit 100;Q3
EXPLAIN ANALYZE
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 100;Q4
EXPLAIN ANALYZE
SELECT
o_orderpriority,
count(*) as order_count
FROM
orders
WHERE
o_orderdate >= date '1993-05-01'
and o_orderdate < date '1993-05-01' + interval '3 month'::interval
and exists (
SELECT
*
FROM
lineitem
WHERE
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
GROUP BY
o_orderpriority
ORDER BY
o_orderpriority;Q5
EXPLAIN ANALYZE
SELECT
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
FROM
customer,
orders,
lineitem,
supplier,
nation,
region
WHERE
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= date '1995-01-01'
and o_orderdate < date '1995-01-01' + '1 year'::interval
GROUP BY
n_name
ORDER BY
revenue desc;Q6
EXPLAIN ANALYZE
SELECT
sum(l_extendedprice * l_discount) as revenue
FROM
lineitem
WHERE
l_shipdate >= date '1993-01-01'
and l_shipdate < date '1993-01-01' + '1 year'::interval
and l_discount between 0.03 - 0.01 and 0.03 + 0.01
and l_quantity < 24;Q7
EXPLAIN ANALYZE
SELECT
supp_nation,
cust_nation,
l_year,
sum(volume) AS revenue
FROM (
SELECT
n1.n_name AS supp_nation,
n2.n_name AS cust_nation,
extract(year FROM l_shipdate) AS l_year,
l_extendedprice * (1 - l_discount) AS volume
FROM
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
WHERE
s_suppkey = l_suppkey
AND o_orderkey = l_orderkey
AND c_custkey = o_custkey
AND s_nationkey = n1.n_nationkey
AND c_nationkey = n2.n_nationkey
AND ((n1.n_name = 'FRANCE'
AND n2.n_name = 'GERMANY')
OR (n1.n_name = 'GERMANY'
AND n2.n_name = 'FRANCE'))
AND l_shipdate BETWEEN CAST('1995-01-01' AS date)
AND CAST('1996-12-31' AS date)) AS shipping
GROUP BY
supp_nation,
cust_nation,
l_year
ORDER BY
supp_nation,
cust_nation,
l_year;Q8
EXPLAIN ANALYZE
SELECT
o_year,
sum(case
when nation = 'INDONESIA' then volume
else 0
end) / sum(volume) as mkt_share
FROM
(
SELECT
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
FROM
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
WHERE
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'ASIA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01'::date and '1996-12-31'::date
and p_type = 'PROMO POLISHED NICKEL'
) as all_nations
GROUP BY
o_year
ORDER BY
o_year;Q9
EXPLAIN ANALYZE
SELECT
nation,
o_year,
sum(amount) as sum_profit
FROM
(
SELECT
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
FROM
part,
supplier,
lineitem,
partsupp,
orders,
nation
WHERE
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%navajo%'
) as profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year desc limit 100;Q10
EXPLAIN ANALYZE
SELECT
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
FROM
customer,
orders,
lineitem,
nation
WHERE
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-08-01'
and o_orderdate < date '1993-08-01' + '3 month'::interval
and l_returnflag = 'R'
and c_nationkey = n_nationkey
GROUP BY
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
ORDER BY
revenue desc
LIMIT 20;Q11
EXPLAIN ANALYZE
SELECT
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
FROM
partsupp,
supplier,
nation
WHERE
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'ALGERIA'
GROUP BY
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
SELECT
sum(ps_supplycost * ps_availqty) * 0.0001000000
FROM
partsupp,
supplier,
nation
WHERE
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'ALGERIA'
)
ORDER BY
value desc;Q12
EXPLAIN ANALYZE
SELECT
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
FROM
orders,
lineitem
WHERE
o_orderkey = l_orderkey
and l_shipmode in ('AIR', 'FOB')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1996-01-01'
and l_receiptdate < date '1996-01-01' + '1 year'::interval
GROUP BY
l_shipmode
ORDER BY
l_shipmode;Q13
EXPLAIN ANALYZE
SELECT
c_count,
count(*) as custdist
FROM
(
SELECT
c_custkey,
count(o_orderkey)
FROM
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%pending%requests%'
GROUP BY
c_custkey
) as c_orders (c_custkey, c_count)
GROUP BY
c_count
ORDER BY
custdist desc,
c_count desc;Q14
EXPLAIN ANALYZE
SELECT
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
FROM
lineitem,
part
WHERE
l_partkey = p_partkey
and l_shipdate >= date '1995-02-01'
and l_shipdate < date '1995-02-01' + interval '1 month'::interval;Q15
EXPLAIN ANALYZE
WITH revenue0 as (
SELECT
l_suppkey as supplier_no,
sum(l_extendedprice * (1 - l_discount)) as total_revenue
FROM
lineitem
WHERE
l_shipdate >= date '1995-08-01'
and l_shipdate < date '1995-08-01' + '3 month'::interval
GROUP BY
l_suppkey)
SELECT
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
FROM
supplier,
revenue0
WHERE
s_suppkey = supplier_no
and total_revenue = (
SELECT
max(total_revenue)
FROM
revenue0
)
ORDER BY
s_suppkey;Q16
EXPLAIN ANALYZE
SELECT
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
FROM
partsupp,
part
WHERE
p_partkey = ps_partkey
and p_brand <> 'Brand#13'
and p_type not like 'ECONOMY BRUSHED%'
and p_size in (11, 8, 10, 31, 21, 13, 32, 28)
and ps_suppkey not in (
SELECT
s_suppkey
FROM
supplier
WHERE
s_comment like '%Customer%Complaints%'
)
GROUP BY
p_brand,
p_type,
p_size
ORDER BY
supplier_cnt desc,
p_brand,
p_type,
p_size limit 100;Q17
EXPLAIN ANALYZE
SELECT
sum(l_extendedprice) / 7.0 as avg_yearly
FROM
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#44'
and p_container = 'MED PKG'
and l_quantity < (
SELECT
0.2 * avg(l_quantity)
FROM
lineitem
WHERE
l_partkey = p_partkey
);Q18
EXPLAIN ANALYZE
SELECT
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey in (
SELECT
l_orderkey
FROM
lineitem
GROUP BY
l_orderkey having
sum(l_quantity) > 313
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
GROUP BY
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
ORDER BY
o_totalprice desc,
o_orderdate limit 100;
--LIMIT 100Q19
EXPLAIN ANALYZE
SELECT
sum(l_extendedprice* (1 - l_discount)) as revenue
FROM
lineitem,
part
WHERE
(
p_partkey = l_partkey
and p_brand = 'Brand#15'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 10 and l_quantity <= 10 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#45'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 18 and l_quantity <= 18 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#21'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 30 and l_quantity <= 30 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
--LIMIT -1Q20
EXPLAIN ANALYZE
SELECT
s_name,
s_address
FROM
supplier,
nation
WHERE
s_suppkey in (
SELECT
ps_suppkey
FROM
partsupp
WHERE
ps_partkey in (
SELECT
p_partkey
FROM
part
WHERE
p_name like 'lemon%'
)
AND ps_availqty > (
SELECT
0.5 * sum(l_quantity)
FROM
lineitem
WHERE
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1997-01-01'
and l_shipdate < date '1997-01-01' + '1 year'::interval
)
)
and s_nationkey = n_nationkey
and n_name = 'INDONESIA'
ORDER BY
s_name limit 100;Q21
EXPLAIN ANALYZE
SELECT
s_name,
count(*) as numwait
FROM
supplier,
lineitem l1,
orders,
nation
WHERE
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
SELECT
*
FROM
lineitem l2
WHERE
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
SELECT
*
FROM
lineitem l3
WHERE
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'INDIA'
GROUP BY
s_name
ORDER BY
numwait desc,
s_name limit 100;
--LIMIT 100Q22
EXPLAIN ANALYZE
SELECT
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
FROM
(
SELECT
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
FROM
customer
WHERE
substring(c_phone from 1 for 2) in
('16', '17', '24', '21', '19', '22', '15')
and c_acctbal > (
SELECT
avg(c_acctbal)
FROM
customer
WHERE
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('16', '17', '24', '21', '19', '22', '15')
)
and not exists (
SELECT
*
FROM
orders
WHERE
o_custkey = c_custkey
)
) as custsale
GROUP BY
cntrycode
ORDER BY
cntrycode;
--LIMIT -1Resultados do teste
Desempenho de consulta
O IMCI proporciona uma aceleração de consulta superior a 50 vezes em comparação ao mecanismo row store. Neste benchmark, todas as 22 consultas TPC-H foram concluídas em 23,92 segundos com o IMCI, contra 2.363,7 segundos no row store — uma aceleração geral de 98,82 vezes.
Detalhamento por consulta (a Q15 atingiu o tempo limite no row store; calculado como 500 segundos):
|
Consulta |
IMCI (segundos) |
Row store (segundos) |
Aceleração |
|
Q1 |
0,98 |
41.629 |
42,48x |
|
Q2 |
0,23 |
78.402 |
340,88x |
|
Q3 |
0,88 |
18.376 |
20,88x |
|
Q4 |
0,78 |
3.929 |
5,04x |
|
Q5 |
0,89 |
14.801 |
16,63x |
|
Q6 |
0,27 |
4.782 |
17,71x |
|
Q7 |
0,86 |
17.661 |
20,54x |
|
Q8 |
0,71 |
21.952 |
30,92x |
|
Q9 |
3,77 |
362,42 |
96,13x |
|
Q10 |
1,41 |
18.313 |
12,99x |
|
Q11 |
0,15 |
8.307 |
55,38x |
|
Q12 |
0,61 |
7.146 |
11,71x |
|
Q13 |
2,31 |
308.555 |
133,57x |
|
Q14 |
0,67 |
10.658 |
15,91x |
|
Q15 |
0,44 |
Tempo esgotado, calculado como 500 |
1136,36x |
|
Q16 |
0,44 |
71.062 |
161,50x |
|
Q17 |
0,84 |
288 |
342,86x |
|
Q18 |
2,58 |
473.446 |
183,51x |
|
Q19 |
1,42 |
0,416 |
0,29x |
|
Q20 |
0,54 |
83 |
153,70x |
|
Q21 |
2,68 |
17.387 |
6,49x |
|
Q22 |
0,46 |
13.458 |
29,26x |
|
Total |
23,92 |
2.363,7 |
98,82x |
Eficiência de armazenamento
A compressão colunar do IMCI reduz significativamente o armazenamento em comparação à tabela heap do row store.
Row store (tabela heap): 126 GB
IMCI (PK desativada): 25 GB — 20% do tamanho do row store. Ideal para dados estáticos.
IMCI (PK ativada): 53 GB — 42% do tamanho do row store. Recomendado para dados dinâmicos.
O parâmetro polar_csi.enable_pk controla se a chave primária é incluída no índice columnstore. Detalhamento por tabela:
|
Tabela |
Linhas |
Row store |
IMCI (PK desativada) |
IMCI (PK ativada) |
|
LINEITEM |
600.037.902 |
86 GB |
17 GB |
36 GB |
|
ORDERS |
150.000.000 |
20 GB |
4406 MB |
9052 MB |
|
PARTSUPP |
80.000.000 |
13 GB |
3452 MB |
6689 MB |
|
PART |
20.000.000 |
3204 MB |
487 MB |
634 MB |
|
CUSTOMER |
15.000.000 |
2808 MB |
992 MB |
1108 MB |
|
SUPPLIER |
1.000.000 |
176 MB |
63 MB |
72 MB |
|
NATION |
25 |
8 KB |
528 KB |
528 KB |
|
REGION |
5 |
8 KB |
528 KB |
528 KB |
|
Total |
866.037.932 |
126 GB |
25 GB |
53 GB |
Tempo de criação de índice
A criação de IMCI em thread única para todas as 8 tabelas leva 39 minutos no total.