pg_clickhouse 0.10: Subquery pushdown va 1000 martta tezroq TPC-H so'rovlari

pg_clickhouse 0.10 yangi versiyasi

pg_clickhouse 0.10 yangi versiyasi chiqdi, unda subquery pushdown va TPC-H so'rovlari 1000 martta tezroq bajariladi.

Subquery pushdown

Subquery pushdown - bu PostgreSQL va ClickHouse o'rtasida so'rovlar bajarilishini tezlashtiradigan texnologiya. U subquerylarni ClickHouse ga yuborish imkonini beradi, bu esa so'rovlar bajarilishini ancha tezlashtiradi.

1EXPLAIN (VERBOSE, COSTS OFF)
2SELECT s.sale_id, s.amount FROM sales s
3WHERE s.amount > (SELECT 1.5 * avg(s2.amount) FROM sales s2
4                  WHERE s2.item_id = s.item_id)
5ORDER BY s.sale_id;

1Foreign Scan on subplan_test.sales s
2   Output: s.sale_id, s.amount
3   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r1 WHERE ((r1.amount > (SELECT (1.5 * avg(q1_1.amount)) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))))) ORDER BY r1.sale_id ASC NULLS LAST
4   SubPlan expr_1
5     ->  Foreign Scan
6           Output: ((1.5 * avg(s2.amount)))
7           Relations: Aggregate on (sales s2)
8           Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32}))
9(8 rows)

TPC-H so'rovlari

TPC-H - bu standartlashtirilgan so'rovlar to'plami, uning yordamida relational ma'lumotlar bazalari ishlashini baholash mumkin. pg_clickhouse 0.10 versiyasi TPC-H so'rovlari 1000 martta tezroq bajarilishini ta'minlaydi.

1EXPLAIN (VERBOSE, COSTS OFF)
2SELECT id FROM tnull WHERE xn NOT IN (1, NULL) ORDER BY id;

1Foreign Scan on in_null_test.tnull
2   Output: id
3   Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN false WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST
4(3 rows)

Yangi funksiyalar

• Subquery pushdown
• TPC-H so'rovlari 1000 martta tezroq bajariladi
• Binary driver yangi versiyasi
• Concurrency bugs tuzatildi

Natijalar

pg_clickhouse 0.10 versiyasi subquery pushdown va TPC-H so'rovlari 1000 martta tezroq bajarilishini ta'minlaydi. Bu yangi versiya PostgreSQL va ClickHouse o'rtasida so'rovlar bajarilishini ancha tezlashtiradi.

1EXPLAIN (VERBOSE, COSTS OFF)
2SELECT id FROM tnull WHERE xn NOT IN (1, 500) ORDER BY id;

1Foreign Scan on in_null_test.tnull
2   Output: id
3   Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((xn NOT IN (1,500))) ORDER BY id ASC NULLS LAST
4(3 rows)

Asl manba: clickhouse.com