PostgreSQL利用递归优化求稀疏列唯一值的方法

在数据库中经常会碰到一些表的列是稀疏列,只有很少的值,例如性别字段,一般就只有2种不同的值。
但是当我们求这些稀疏列的唯一值时,如果表的数据量很大,速度还是会很慢。

例如:
创建测试表

bill=# create table t_sex (sex char(1), otherinfo text);
CREATE TABLE
bill=# insert into t_sex select 'm', generate_series(1,10000000)||'this is test';
INSERT 0 10000000
bill=# insert into t_sex select 'w', generate_series(1,10000000)||'this is test';
INSERT 0 10000000

查询:
可以看到下面的查询速度很慢。

bill=# select count(distinct sex) from t_sex;
 count
-------
   2
(1 row)

Time: 8803.505 ms (00:08.804)
bill=# select sex from t_sex t group by sex;
 sex
-----
 m
 w
(2 rows)

Time: 1026.464 ms (00:01.026)

那么我们对该字段加上索引又是什么情况呢?

速度依然没有明显

bill=# create index idx_sex_1 on t_sex(sex);
CREATE INDEX
bill=# select count(distinct sex) from t_sex;
 count
-------
   2
(1 row)

Time: 8502.460 ms (00:08.502)
bill=# select sex from t_sex t group by sex;
 sex
-----
 m
 w
(2 rows)

Time: 572.353 ms

的变化,可以看到执行计划已经使用Index Only Scan了。

bill=# explain select count(distinct sex) from t_sex;
                     QUERY PLAN
----------------------------------------------------------------------------------------------
 Aggregate (cost=371996.44..371996.45 rows=1 width=8)
  -> Index Only Scan using idx_sex_1 on t_sex (cost=0.44..321996.44 rows=20000000 width=2)
(2 rows)

同样的SQL我们看看在Oracle中性能如何?

创建测试表:

SQL> create table t_sex (sex char(1), otherinfo varchar2(100));

Table created.

SQL> insert into t_sex select 'm', rownum||'this is test' from dual connect by level <=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> insert into t_sex select 'w', rownum||'this is test' from dual connect by level <=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

性能测试:

SQL> set lines 1000 pages 2000
SQL> set autotrace on
SQL> set timing on

SQL> select count(distinct sex) from t_sex;

COUNT(DISTINCTSEX)
------------------
         2

Elapsed: 00:00:01.58

Execution Plan
----------------------------------------------------------
Plan hash value: 3915432945

----------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |    |   1 |   3 | 20132  (1)| 00:00:01 |
|  1 | SORT GROUP BY   |    |   1 |   3 |      |     |
|  2 |  TABLE ACCESS FULL| T_SEX |  14M|  42M| 20132  (1)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
   74074 consistent gets
     0 physical reads
     0 redo size
    552 bytes sent via SQL*Net to client
    608 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     1 sorts (memory)
     0 sorts (disk)
     1 rows processed

SQL> select sex from t_sex t group by sex;

SE
--
m
w

Elapsed: 00:00:01.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3915432945

----------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |    |  14M|  42M| 20558  (3)| 00:00:01 |
|  1 | SORT GROUP BY   |    |  14M|  42M| 20558  (3)| 00:00:01 |
|  2 |  TABLE ACCESS FULL| T_SEX |  14M|  42M| 20132  (1)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
   74074 consistent gets
     0 physical reads
     0 redo size
    589 bytes sent via SQL*Net to client
    608 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     1 sorts (memory)
     0 sorts (disk)
     2 rows processed

可以看到Oracle的性能即使不加索引也明显比PostgreSQL中要好。
那么我们在PostgreSQL中是不是没办法继续优化了呢?这种情况我们利用pg中的递归语句结合索引可以大幅提升性能。

SQL改写:

bill=# with recursive tmp as (
bill(#  (
bill(#   select min(t.sex) as sex from t_sex t where t.sex is not null
bill(#  )
bill(#  union all
bill(#  (
bill(#   select (select min(t.sex) from t_sex t where t.sex > s.sex and t.sex is not null)
bill(#    from tmp s where s.sex is not null
bill(#  )
bill(# )
bill-# select count(distinct sex) from tmp;
 count
-------
   2
(1 row)

Time: 2.711 ms

查看执行计划:

bill=# explain with recursive tmp as (
bill(#  (
bill(#   select min(t.sex) as sex from t_sex t where t.sex is not null
bill(#  )
bill(#  union all
bill(#  (
bill(#   select (select min(t.sex) from t_sex t where t.sex > s.sex and t.sex is not null)
bill(#    from tmp s where s.sex is not null
bill(#  )
bill(# )
bill-# select count(distinct sex) from tmp;
                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=53.62..53.63 rows=1 width=8)
  CTE tmp
   -> Recursive Union (cost=0.46..51.35 rows=101 width=32)
      -> Result (cost=0.46..0.47 rows=1 width=32)
         InitPlan 3 (returns $1)
          -> Limit (cost=0.44..0.46 rows=1 width=2)
             -> Index Only Scan using idx_sex_1 on t_sex t (cost=0.44..371996.44 rows=20000000 width=2)
                Index Cond: (sex IS NOT NULL)
      -> WorkTable Scan on tmp s (cost=0.00..4.89 rows=10 width=32)
         Filter: (sex IS NOT NULL)
  -> CTE Scan on tmp (cost=0.00..2.02 rows=101 width=32)
(11 rows)

Time: 1.371 ms

可以看到执行时间从原先的8000ms降低到了2ms,提升了几千倍!

甚至对比Oracle,性能也是提升了很多。

但是需要注意的是:这种写法仅仅是针对稀疏列,换成数据分布广泛的字段,显然性能是下降的, 所以使用递归SQL不适合数据分布广泛的字段的group by或者count(distinct)操作。

到此这篇关于PostgreSQL利用递归优化求稀疏列唯一值的文章就介绍到这了,更多相关PostgreSQL递归优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • PostgreSQL图(graph)的递归查询实例

    背景 在树形递归查询这篇文章,我记录了使用CTE语法查询树形结构的办法.在一个树形结构中,每一个节点最多有一个上级,可以有任意个数的下级. 在实际场景中,我们还会遇到对图(graph)的查询,图和树的最大区别是,图的节点可以有任意个数的上级和下级.如下图所示 因为图可能存在loop结构(上图红色箭头),所以在使用CTE递归的过程中,必须要破环(break loop),否则算法就会进入无限递归,永不结束. 存储和查询图结构,目前当红数据库是neo4j,但是当数据量只有十几万条的时候,Postgre

  • 在PostgreSQL中实现递归查询的教程

     介绍 在Nilenso,哥在搞一个 (开源的哦!)用来设计和发起调查的应用. 下面这个是一个调查的例子: 在内部,它是这样表示滴: 一个调查包括了许多问题(question).一系列问题可以归到(可选)一个分类(category)中.我们实际的数据结构会复杂一点(特别是子问题sub-question部分),但先当它就只有question跟category吧. 我们是这样保存question跟category的. 每个question和category都有一个order_number字段.是个整

  • PostgreSQL树形结构的递归查询示例

    背景 处理不确定深度的层级结构,比如组织机构,一个常用的设计是在一张表里面保存 ID 和 Parent_ID ,并且通过自联结的办法构造一颗树.这种方式对写数据的过程很友好,但是查询过程就变得相对复杂.在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点. Oracle提供的connect by扩展语法,简单好用.但是其他的RDBMS就没这么人性化了(或者我不知道).最近在项目中使用PostgreSQL来查询树形数据,记录一下. 构造样本数据 drop table if ex

  • PostgreSQL利用递归优化求稀疏列唯一值的方法

    在数据库中经常会碰到一些表的列是稀疏列,只有很少的值,例如性别字段,一般就只有2种不同的值. 但是当我们求这些稀疏列的唯一值时,如果表的数据量很大,速度还是会很慢. 例如: 创建测试表 bill=# create table t_sex (sex char(1), otherinfo text); CREATE TABLE bill=# insert into t_sex select 'm', generate_series(1,10000000)||'this is test'; INSER

  • js利用递归与promise 按顺序请求数据的方法

    问题:项目中有一个需求,一个tabBar下面如果没有内容就不让该tabBar显示,当然至于有没有内容,需要我们通过请求的来判断,但是由于请求是异步的,如何让请求按照tabBar的顺序进行? 方案:我们可以将promise变成下一个请求,可以利用递归来实现 实施: //定义初始数据 requestlist就像tabBar列表 let requestlist = [1, 2, 3, 4, 5, 6, 7,8,9]; //每个tabBar的返回数据使用reslist装起来 let reslist =

  • C#生成唯一值的方法汇总

    生成唯一值的方法很多,下面就不同环境下生成的唯一标识方法一一介绍,作为工作中的一次总结,有兴趣的可以自行测试: 一.在 .NET 中生成 1.直接用.NET Framework 提供的 Guid() 函数,此种方法使用非常广泛.GUID(全局统一标识符)是指在一台机器上生成的数字,它保证对在同一时空中的任何两台计算机都不会生成重复的 GUID 值(即保证所有机器都是唯一的).关于GUID的介绍在此不作具体熬述,想深入了解可以自行查阅MSDN.代码如下: 复制代码 代码如下: using Syst

  • JavaScript获取table中某一列的值的方法

    1.实现源码 复制代码 代码如下: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-eq

  • Django ORM 查询表中某列字段值的方法

    1.什么是ORM ORM 全拼Object-Relation Mapping. 中文意为 对象-关系映射. 在MVC/MVT设计模式中的Model模块中都包括ORM 2.ORM优势 (1)只需要面向对象编程, 不需要面向数据库编写代码. 对数据库的操作都转化成对类属性和方法的操作. 不用编写各种数据库的sql语句. (2)实现了数据模型与数据库的解耦, 屏蔽了不同数据库操作上的差异. 不在关注用的是mysql.oracle...等. 通过简单的配置就可以轻松更换数据库, 而不需要修改代码. 3.

  • C++利用递归实现走迷宫

    本文实例为大家分享了C++利用递归实现走迷宫的具体代码,供大家参考,具体内容如下 要求: 1.将地图的数组保存在文件中,从文件中读取行列数 2..动态开辟空间保存地图 3..运行结束后再地图上标出具体的走法 说明: 1.文件中第一行分别放置的是地图的行数和列数 2.其中1表示墙,即路不通,0表示路,即通路 3.程序运行结束后用2标记走过的路径 4.当走到"死胡同"时用3标记此路为死路 5.每到一个点,按照 左 上 右 下 的顺序去试探 6.没有处理入口就是"死胡同"

  • python利用递归方法实现求集合的幂集

    什么是集合的幂集? 就是原集合中所有的子集(bai包括全集du和空集)构成的集族.可数集是zhi最小的无限集: 它的幂集和实数dao集一一对应(也称同势),是不可数集. 不是所有不可数集都和实数集等势,集合的势可以无限的大.如实数集的幂集也是不可数集,但它的势比实数集大. 设X是一个有限集,|X| = k,则X的幂集的势为2的k次方. 代码 def powSet(S): #创建列表a存储S中的元素 a=[] for i in S: a.append(i) #判断S中是否只有一个元素,作为递归的终

  • 在PostgreSQL中设置表中某列值自增或循环方式

    在postgresql中,设置已存在的某列(num)值自增,可以用以下方法: //将表tb按name排序,利用row_number() over()查询序号并将该列命名为rownum,创建新表tb1并将结果保存到该表中 create table tb1 as (select *, row_number() over(order by name) as rownum from tb); //根据两张表共同的字段name,将tb1中rownum对应值更新到tb中num中 update tb set

  • postgresql高级应用之行转列&汇总求和的实现思路

    前言 节前公司业务方需要做一個統計報表,这个报表用于统计当月估计几个明星品的销售情况,而我们的数据是按行存储的就是日期|产品|渠道|销售额这样,说是也奇了怪了,我们买的报(guan)表(yuan)系(la)统(ji) 竟然不能容易地实现...,于是我看了看,然后想了想,发现是可以通过sql算出这样一个报表(多亏了postgresql的高阶函数

  • java递归法求字符串逆序

    本文实例讲述了java递归法求字符串逆序的方法.分享给大家供大家参考.具体实现方法如下: public static String reverseString(String x) { if(x==null || x.length()<2) return x; return reverseString(x.substring(1,x.length()))+ x.charAt(0); } 希望本文所述对大家的java程序设计有所帮助.

随机推荐