31 posts tagged with "pawsql"

PostgreSQL​

数据访问​

SEQ SCAN: 通过顺序扫描输入记录集来查找相关记录，顺序扫描（与索引扫描不同）执行单个读取操作。 CTE SCAN: 对公共表达式（CTE）查询结果执行顺序扫描。请注意，CTE的结果被计算并临时存储(Materialization)。 INDEX SCAN: 根据索引查找相关记录。索引扫描执行两次读取操作：一次读取索引，另一次从表中读取实际值。

Channel of advanced SQL tuning

Overview​

NULL value processing is the most error-prone for database application developers, mainly because we are accustomed to using binary Boolean logic, while the database's processing logic for NULL values is three-valued logic. In fact, the most flawed component in the database optimizers is the logic related to NULL value processing. Even mature database software, such as DB2/Teradata, still has more than 20% of the bugs related NULL processing.

In this article, we analyze the root causes of the NULL value pitfalls, and conclude with a simple and effective examination logic to infer the final result. At the same time, we explain the applicable conditions and solutions for the four common scenarios in daily development work. After reading this article, you will be able to cope with all the scenarios regarding NULL value handling in your daily SQL processing work.

Copyright © 2023 PawSQL

Definition​

Natural join is a special type of equijoin that can be used with inner, outer and full joins. It automatically searches for all columns in both tables with the same name and type and performs an equijoin on those columns. Natural join can simplify the statement but implicit join conditions reduce code readability and understanding of relationships between tables, and are also prone to mistaken joins. PawSQL provides a risk warning for statements using natural join to avoid potential correctness issues caused by it.

Copyright © 2023 PawSQL

Problem Definition​

For ORDER BY clauses, all expressions must be sorted in the same ASC or DESC direction to utilize indexes. If an ORDER BY statement uses different sort directions on multiple conditions, indexes cannot be used.

For example, creating an index on the lineitem table in TPCH:

create index l_partkey_suppkey_idx on lineitem(l_partkey, l_suppkey);

Copyright © 2023 PawSQL

Problem Definition​

When using COUNT(DISTINCT) on multiple columns, the result may differ from what you expect. COUNT(DISTINCT col) counts the number of non-NULL distinct values in that column, while COUNT(DISTINCT col, col2) will exclude rows where either column is NULL.

For example, in the following query counting distinct values in column a and column combination (a,b):

select count(distinct t.a) a_cnt, count(distinct t.a,t.b) a_b_cnt from (values row(1,2),row(3,null)) as t(a,b);

The result is 2 distinct values for column a, but only 1 distinct value for column combination (a,b). This may not match your expectation.