NOT-IN expressions with subquery in SQL are notorious but very common. Not only do programmers write such kinds of SQLs but also many BI applications generate the kinds of SQLs. With NULL values, things get much more complex and even much much more complex for MPP databases. In this blog, we focus on single-node Postgres to understand the data structures, semantics, and algorithms of this topic.
I will talk more about GPDB’s LASJ implementation of NOT-IN later.
Not sure why I came again to this interesting paper One WITH RECURSIVE is Worth Many GOTOs. Daily work on Greenplum makes me think and inspires me to learn knowledge in different areas of computer science. This paper was accepted in the same SIGMOD as Greenplum’s HTAP paper and I have a vague impression of it. Recently three tasks in my work made me peruse the paper:
the labels as values skill in Greenplum(Postgres)’s expression valuationThis paper uses recursive CTE, Lateral Join and Union All with filters to implement control structure such as Loop, Condition in PLPGSQL. So now is a good time to start a new tour.
我已经不止一次有这样的感觉了: 当遇到一个特性类型的bug后，短时间内会频繁遇到本质一样的bug。这次是Greenplum关于Opclass和Opfamily的话题。这种模式不是偶然，我有一个猜想来解释：遇到第一个问题的时候，如果能够深入仔细的研究思考，由点及面，然后这部分储备的知识和思维的训练，让你更容易从代码和issue里看出同类问题。
本文介绍Greenplum Database里的Opclass和Opfamily相关概念，然后列出Greenplum开源社区仓库 greenplum-db/gpdb 里一些关于这个话题的issue并逐一诊断分析。各小节独立自成一个话题，小节之间可能会显得跳跃。不同的参考资料也将散落在各个小节内部。Issue修复的过程是动态的，我会把在开源社区出现过的东西online session里讨论清楚并公布录屏。
Recently I have been working on several issues involving relation_size in Greenplum, this post is to summarize some interesting parts of the topic. I also post a thread on gpdb-dev mailing list: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/DkTx4O-kuH0.
Q: there is a table storing some other tables’ oid, how to write a SQL to compute table size for each oid?
This problem is so simple in Postgres (single instance), but it turns much more complex than the first glance to work this out high efficiently in Greenplum.
Greenplum is MPP database. The SQL query’s planning and executing model is a distributed computing model. Greenplum introduces the concept of locus to model a path’s data distribution.
For more details, please refer to one of my talk Greenplum 查询优化器关键技术介绍.
Continue reading标题党实锤了。这也是我博客中第一篇用中文写的技术文章。坚持用英文写博客，并不是崇洋媚外，只是为了跟各国同事之间交流技术方便一些。那么这次为啥选择母语，因为原始论文里的例子一点也不好玩，我选择三国赵云传的一个数据来作例子，那不妨就写得幽默一代呢，放飞自我放飞的根本一些。
论文是这篇: RaSQL: Greater Power and Performance for Big Data Analytics with Recursive-aggregate-SQL on Spark, SIGMOD 19.
Continue readingRecently I found a Bug of Postgres, to fully understand it and fix it, I spend some time on Postgres operator class and operator family concepts. I find it very similar to SML’s module system. So this blog records my thoughts.
Continue readingRecently I am busy with some escalations on lateral join in Greenplum. During the process of studying the related code and issues, many problems intertwine. These are common issues in an MPP database.
A more recent blog should be better: Interconnect Deadlock Problem in Greenplum.
Continue readingGreenplum database is the world’s leading open-source MPP database. MPP database means a distributed system. They very first thing you have to deal with in distributed system is that how to divide data into pieces and split them around machines. Greenplum supports different kinds of data distribution policy: hash, random, replicated. Among these kinds of policy, hash-distribution is the most important one since many intermediate results are hash-distributed. Please visit https://greenplum.cn/ for more details.
Continue readingComputer science might be said born at 1930s, in the math college of Princeton University. Church, Turing, Gödel independently discovered the root of computer science:
They all relates to the problem: self reference or self description. We might call it recurrence too.
Continue reading