Hologres:EXPLAIN and EXPLAIN ANALYZE

EXPLAIN <sql>;

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' - interval '120' day
GROUP BY
        l_returnflag,
        l_linestatus
ORDER BY
        l_returnflag,
        l_linestatus;

QUERY PLAN
Sort  (cost=0.00..7795.30 rows=3 width=80)
  Sort Key: l_returnflag, l_linestatus
  ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
        ->  Project  (cost=0.00..7795.27 rows=3 width=80)
              ->  Project  (cost=0.00..7794.27 rows=3 width=104)
                    ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                          Group Key: l_returnflag, l_linestatus
                          ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                Hash Key: l_returnflag, l_linestatus
                                ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                      Group Key: l_returnflag, l_linestatus
                                      ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                            ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                  ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                        Group Key: l_returnflag, l_linestatus
                                                        ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                              ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                    ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                          Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)

EXPLAIN ANALYZE <sql>;

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' - interval '120' day
GROUP BY
        l_returnflag,
        l_linestatus
ORDER BY
        l_returnflag,
        l_linestatus;

QUERY PLAN
Sort  (cost=0.00..7795.30 rows=3 width=80)
  Sort Key: l_returnflag, l_linestatus
[id=21 dop=1 time=2427/2427/2427ms rows=4(4/4/4) mem=3/3/3KB open=2427/2427/2427ms get_next=0/0/0ms]
  ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
      [20:1 id=100003 dop=1 time=2426/2426/2426ms rows=4(4/4/4) mem=1/1/1KB open=0/0/0ms get_next=2426/2426/2426ms]
        ->  Project  (cost=0.00..7795.27 rows=3 width=80)
            [id=19 dop=20 time=2427/2426/2425ms rows=4(1/0/0) mem=87/87/87KB open=2427/2425/2425ms get_next=1/0/0ms]
              ->  Project  (cost=0.00..7794.27 rows=0 width=104)
                    ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                          Group Key: l_returnflag, l_linestatus
                        [id=16 dop=20 time=2427/2425/2424ms rows=4(1/0/0) mem=574/570/569KB open=2427/2425/2424ms get_next=1/0/0ms]
                          ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                Hash Key: l_returnflag, l_linestatus
                              [20:20 id=100002 dop=20 time=2427/2424/2423ms rows=80(20/4/0) mem=3528/1172/584B open=1/0/0ms get_next=2426/2424/2423ms]
                                ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                      Group Key: l_returnflag, l_linestatus
                                    [id=12 dop=20 time=2428/2357/2256ms rows=80(4/4/4) mem=574/574/574KB open=2428/2357/2256ms get_next=1/0/0ms]
                                      ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                          [id=11 dop=20 time=2427/2356/2255ms rows=936(52/46/44) mem=7/6/6KB open=0/0/0ms get_next=2427/2356/2255ms pull_dop=9/9/9]
                                            ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                [id=8 dop=234 time=2435/1484/5ms rows=936(4/4/4) mem=0/0/0B open=2435/1484/5ms get_next=4/0/0ms]
                                                  ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                        Group Key: l_returnflag, l_linestatus
                                                      [id=5 dop=234 time=2435/1484/3ms rows=936(4/4/4) mem=313/312/168KB open=2435/1484/3ms get_next=0/0/0ms]
                                                        ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                            [id=4 dop=234 time=2145/1281/2ms rows=585075720(4222846/2500323/3500) mem=142/141/69KB open=10/1/0ms get_next=2145/1280/2ms]
                                                              ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                  [id=3 dop=234 time=582/322/2ms rows=585075720(4222846/2500323/3500) mem=142/142/69KB open=10/1/0ms get_next=582/320/2ms]
                                                                    ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                          Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                        [id=2 dop=234 time=259/125/1ms rows=585075720(4222846/2500323/3500) mem=1418/886/81KB open=10/1/0ms get_next=253/124/0ms]

ADVICE:
[node id : 1000xxx] distribution key miss match! table lineitem defined distribution keys : l_orderkey; request distribution columns : l_returnflag, l_linestatus;
shuffle data skew in different shards! max rows is 20, min rows is 0

Query id:[300200511xxxx]
======================cost======================
Total cost:[2505] ms
Optimizer cost:[47] ms
Init gangs cost:[4] ms
Build gang desc table cost:[2] ms
Start query cost:[18] ms
- Wait schema cost:[0] ms
- Lock query cost:[0] ms
- Create dataset reader cost:[0] ms
- Create split reader cost:[0] ms
Get the first block cost:[2434] ms
Get result cost:[2434] ms
====================resource====================
Memory: 921(244/230/217) MB,  straggler worker id: 72969760xxx
CPU time: 149772(38159/37443/36736) ms, straggler worker id: 72969760xxx
Physical read bytes: 3345(839/836/834) MB, straggler worker id: 72969760xxx
Read bytes: 41787(10451/10446/10444) MB, straggler worker id: 72969760xxx
DAG instance count: 41(11/10/10), straggler worker id: 72969760xxx
Fragment instance count: 275(70/68/67), straggler worker id: 72969760xxx

QUERY PLAN

EXPLAIN gibi planı aşağıdan yukarıya okuyun. Her düğüm iki satır gösterir:

• İyileştirici tahminleri (cost=... rows=... width=...): EXPLAIN ile aynı anlamdadır.

• Gerçek çalışma zamanı ölçümleri [id=... dop=... time=... rows=... mem=... open=... get_next=...]: Çalıştırma sırasında toplanan gerçek değerler.

Çalışma zamanı ölçüm alanları şunlardır:

Bir işlecin time değeri, alt işleçlerinin kümülatif zamanını içerir. Tek bir işlecin maliyetini izole etmek için alt işlecin zamanını çıkarın. rows ve mem değerleri işleç başına olup kümülatif değildir.

ADVICE

ADVICE bölümü, mevcut çalıştırmadan türetilen otomatik ayarlama önerilerini listeler:

• Eksik veya uyuşmayan dağıtım anahtarları, kümeleme anahtarları veya bitmap dizinleri (örneğin, Table xxx misses bitmap index)

• Eski tablo istatistikleri (Table xxx Miss Stats! please run 'analyze xxx')

• Veri eğriliği (shuffle data skew in different shards! max rows is 20, min rows is 0)

Bu öneriler yalnızca mevcut sorguya dayanır. Değişiklikleri uygulamadan önce bunları kendi iş yükünüzle karşılaştırarak gözden geçirin.

Maliyet dökümü

Maliyet bölümü, sorgu süresinin nerede harcandığını gösterir:

Kaynak tüketimi

Kaynak ölçümleri total(max/avg/min) formatını kullanır; burada her değer en yüksek tüketime sahip çalışanı, çalışan başına ortalamayı ve en düşük tüketime sahip çalışanı kapsar.

Seq scan

Seq Scan, herhangi bir dizin kullanmadan tüm tabloyu okur.

EXPLAIN SELECT * FROM public.holo_lineitem_100g;

Bölümlenmiş tablolar Seq Scan on Partitioned Table işlecini gösterir ve "Partitions selected: x out of y" raporlar:

EXPLAIN SELECT * FROM public.hologres_parent;

Harici tablolar, kaynağı (MaxCompute, OSS veya Hologres) tanımlayan bir Foreign Table Type etiketi içerir:

EXPLAIN SELECT * FROM public.odps_lineitem_100;

Index Scan ve Index Seek

Hologres, tablonun depolama formatına bağlı olarak farklı dizin işleçleri kullanır:

• Index Scan (Clustering_index): Sütun deposu tablolar için kullanılır. Bir sorgu bir dizine uydığında görünür. Plan, hangi dizinin kullanıldığını belirleyen alt işleçleri gösterir: Segment Filter (segment dizini), Cluster Filter (kümeleme dizini) veya Bitmap Filter (bitmap dizini). Daha fazla bilgi için bkz. Sütun deposu ilkeleri. Örnek 1: Sorgu kümeleme dizinine vurur.

  BEGIN;
  CREATE TABLE column_test (
   "id" bigint not null ,
   "name" text not null ,
   "age" bigint not null
  );
  CALL set_table_property('column_test', 'orientation', 'column');
  CALL set_table_property('column_test', 'distribution_key', 'id');
  CALL set_table_property('column_test', 'clustering_key', 'id');
  COMMIT;
  
  INSERT INTO column_test VALUES(1,'tom',10),(2,'tony',11),(3,'tony',12);
  
  EXPLAIN SELECT * FROM column_test WHERE id>2;

  Örnek 2: Dizine alınmamış bir sütunda sorgu—planda Clustering_index yok.

  EXPLAIN SELECT * FROM column_test WHERE age>10;

  

  

• Index Seek (pk_index): Birincil anahtar dizinlerine sahip satır deposu tablolar için kullanılır. Birincil anahtar sorgusu Fixed Plan kullanmadığında görünür. Daha fazla bilgi için bkz. Satır deposu ilkeleri.

  BEGIN;
  CREATE TABLE row_test_1 (
      id bigint not null,
      name text not null,
      class text ,
  PRIMARY KEY (id)
  );
  CALL set_table_property('row_test_1', 'orientation', 'row');
  CALL set_table_property('row_test_1', 'clustering_key', 'name');
  COMMIT;
  INSERT INTO row_test_1 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
  
  BEGIN;
  CREATE TABLE row_test_2 (
      id bigint not null,
      name text not null,
      class text ,
  PRIMARY KEY (id)
  );
  CALL set_table_property('row_test_2', 'orientation', 'row');
  CALL set_table_property('row_test_2', 'clustering_key', 'name');
  COMMIT;
  INSERT INTO row_test_2 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
  
  --primary key index
  EXPLAIN SELECT * FROM (SELECT id FROM row_test_1 WHERE id = 1) t1 JOIN row_test_2 t2 ON t1.id = t2.id;

  

Filter

Düz bir Filter koşulun herhangi bir dizine uymadığı anlamına gelir. Performansı artırmak için tablonun dizin yapılandırmasını kontrol edin ve uygun dizinler ekleyin.

Plan One-Time Filter: false gösteriyorsa, sonuç kümesi boştur.

BEGIN;
CREATE TABLE clustering_index_test (
 "id" bigint not null ,
 "name" text not null ,
 "age" bigint not null
);
CALL set_table_property('clustering_index_test', 'orientation', 'column');
CALL set_table_property('clustering_index_test', 'distribution_key', 'id');
CALL set_table_property('clustering_index_test', 'clustering_key', 'age');
COMMIT;

INSERT INTO clustering_index_test VALUES (1,'tom',10),(2,'tony',11),(3,'tony',12);

EXPLAIN SELECT * FROM clustering_index_test WHERE id>2;

Segment Filter

Sorgu bir segment dizinine (Event Time Column) uymuştur. Index Scan ile birlikte görünür. Bkz. Event Time Column (segment key).

Cluster Filter

Sorgu bir kümeleme dizinine uymuştur. Bkz. Clustering key.

Bitmap Filter

Sorgu bir bitmap dizinine uymuştur. Bkz. Bitmap index.

Join Filter

Bir birleştirme işleminden sonra ek filtreleme uygular.

EXPLAIN SELECT * FROM public.lineitem;

BEGIN;
CREATE TABLE tbl1(
a int not null,
b text not null
);
CALL set_table_property('tbl1', 'distribution_key', 'a');
CREATE TABLE tbl2(
c int not null,
d text not null
);
CALL set_table_property('tbl2', 'distribution_key', 'd');
COMMIT;

EXPLAIN SELECT * FROM tbl1  JOIN tbl2 ON tbl1.a=tbl2.c;

Hash Join

Hash Join, bellekte küçük tablodan bir hash tablosu oluşturur, ardından büyük tablodan gelen verilerle satır satır sorgular.

Bir Hash Join'i analiz ederken ayrıca şunları kontrol edin:

• hash cond: Birleştirme koşulu, örneğin hash cond (tmp.a=tmp1.b).

• hash key: Parçalar arasında hash hesaplama için kullanılan anahtar, tipik olarak GROUP BY anahtarıdır.

Küçük tablo hash tablosu olmalıdır. Doğrulamak için planda hash olarak etiketlenen tabloyu arayın—aşağıdan yukarıya okurken alt dalda görünür. Daha büyük tabloyu hash tablosu olarak kullanmak aşırı bellek tüketir.

Ayarlama: istatistikleri güncelleyin

Eski istatistikler, QO'nun tablo boyutlarını yanlış değerlendirmesine neden olur. Örneğin, istatistikler aslında 1 milyon satırı olan bir tablo için rows=1000 gösterirse, hash tablosu olarak yanlış tablo seçilir.

BEGIN ;
CREATE TABLE public.hash_join_test_1 (
    a integer not null,
    b text not null
);
CALL set_table_property('public.hash_join_test_1', 'distribution_key', 'a');
CREATE TABLE public.hash_join_test_2 (
    c integer not null,
    d text not null
);
CALL set_table_property('public.hash_join_test_2', 'distribution_key', 'c');
COMMIT ;

INSERT INTO hash_join_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
INSERT INTO hash_join_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);

EXPLAIN SELECT * FROM hash_join_test_1 tbl1  JOIN hash_join_test_2 tbl2 ON tbl1.a=tbl2.c;

Plan, yanlışlıkla daha büyük olan hash_join_test_2 tablosunu hash tablosu olarak kullanır:

İstatistikler yenilendikten sonra plan doğru şekilde daha küçük tabloyu seçer:

ANALYZE hash_join_test_1;
ANALYZE hash_join_test_2;

Ayarlama: karmaşık sorgular için birleştirme sırasını ayarlayın

İstatistiklerin yenilenmesi çoğu birleştirme sırası sorununu çözer. Beş veya daha fazla tablo içeren karmaşık birleştirmeler için QO, en iyi planı bulmak için önemli ölçüde zaman harcayabilir. Bunu kontrol etmek için optimizer_join_order Grand Unified Configuration (GUC) parametresini kullanın:

SET optimizer_join_order = '<value>';

Nested Loop Join ve Materialize

Nested Loop işleci, dış tabloyu satır satır okur ve her eşleşme için iç tabloyu arar—temelde bir Kartezyen çarpımdır. İç tablo genellikle planda bir Materialize işleci gösterir.

BEGIN;
CREATE TABLE public.nestedloop_test_1 (
    a integer not null,
    b integer not null
);
CALL set_table_property('public.nestedloop_test_1', 'distribution_key', 'a');
CREATE TABLE public.nestedloop_test_2 (
    c integer not null,
    d text not null
);
CALL set_table_property('public.nestedloop_test_2', 'distribution_key', 'c');
COMMIT;

INSERT INTO nestedloop_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
INSERT INTO nestedloop_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);

EXPLAIN SELECT * FROM nestedloop_test_1 tbl1,nestedloop_test_2 tbl2 WHERE tbl1.a>tbl2.c;

Nested Loop maliyetini azaltmak için:

• İç tablonun taranma sayısını sınırlamak için dış sonuç kümesini küçük tutun.

• Mümkün olduğunca non-equi birleştirmelerden kaçının—genellikle Nested Loop planları üretirler.

Cross Join

V3.0'dan başlayarak, Cross Join işleci küçük bir tablo içeren non-equi birleştirme senaryolarını optimize eder. Her dış satır için iç durumu sıfırlayan Nested Loop'un aksine, Cross Join tüm küçük tabloyu belleğe yükler ve büyük tabloyu üzerinden akıtır. Bu çok daha hızlıdır ancak daha fazla bellek kullanır.

Cross Join'u devre dışı bırakmak için:

-- Disable for the current session
SET hg_experimental_enable_cross_join_rewrite = off;

-- Disable at the database level (takes effect for new connections)
ALTER database <database name> hg_experimental_enable_cross_join_rewrite = off;

Merge Join

Merge Join, girişlerinin birleştirme anahtarlarında önceden sıralanmış olmasını gerektirir. Hologres, giriş verilerinin zaten sıralı olduğu sorgular için Merge Join'i seçebilir.

BEGIN;
CREATE TABLE broadcast_test_1 (
    f1 int,
    f2 int);
CALL set_table_property('broadcast_test_1','distribution_key','f2');
CREATE TABLE broadcast_test_2 (
    f1 int,
    f2 int);
COMMIT;

INSERT INTO broadcast_test_1 SELECT i AS f1, i AS f2 FROM generate_series(1, 30)i;
INSERT INTO broadcast_test_2 SELECT i AS f1, i AS f2 FROM generate_series(1, 30000)i;

ANALYZE broadcast_test_1;
ANALYZE broadcast_test_2;

EXPLAIN SELECT * FROM broadcast_test_1 t1, broadcast_test_2 t2 WHERE t1.f1=t2.f1;

CREATE TABLE pqe_test(a text);
INSERT INTO pqe_test VALUES ('2023-01-28 16:25:19.082698+08');
EXPLAIN SELECT a::timestamp FROM pqe_test;

EXPLAIN SELECT to_timestamp(a,'YYYY-MM-DD HH24:MI:SS') FROM pqe_test;

EXPLAIN SELECT
        sum(l_extendedprice * l_discount) AS revenue
FROM
        lineitem
WHERE
        l_shipdate >= date '1996-01-01'
        AND l_shipdate < date '1996-01-01' + interval '1' year
        AND l_discount BETWEEN 0.02 - 0.01 AND 0.02 + 0.01
        AND l_quantity < 24;

SET optimizer_force_multistage_agg = on;

EXPLAIN SELECT l_shipdate FROM public.lineitem ORDER BY l_shipdate;

EXPLAIN SELECT * FROM public.lineitem limit 1;