posggres 的聚合查询，记录数好奇怪：

我的测试环境如何产生数据的：用sysbench生成和测试过！

sysbench  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=test02 --pgsql-password=test02 --pgsql-db=postgres --oltp-table-size=200000 --oltp-tables-count=10 --rand-init=on --threads=10 --time=30 --events=0 --report-interval=10 --percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua prepare
sysbench  --db-driver=pgsql  --pgsql-host=127.0.0.1  --pgsql-port=5432  --pgsql-user=test02  --pgsql-password=test02   --pgsql-db=postgres  --oltp-table-size=10000  --threads=10  --time=12000  --events=0  --report-interval=10  --percentile=99  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua  run

表的结构和表的数量如下：

CommSQLPlus > select count(*) from sbtest2;
CommSQLPlus >
select count(*) from sbtest2;
+----------+
|count     |
+----------+
|200000    |
+----------+
当前查询记录数量：1

CommSQLPlus > select count(*) from sbtest1;
CommSQLPlus >
select count(*) from sbtest1;
+----------+
|count     |
+----------+
|200000    |
+----------+
当前查询记录数量：1
CommSQLPlus >
表结构如下：
CommSQLPlus > desc sbtest1
CommSQLPlus >
CommSQLPlus >
+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
|table_schema    |table_name    |column_name    |data_type    |is_nullable    |column_default                         |last_analyze    |
+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
|public          |sbtest1       |pad            |character    |NO             |''::bpchar                             |                |
|public          |sbtest1       |c              |character    |NO             |''::bpchar                             |                |
|public          |sbtest1       |k              |integer      |NO             |0                                      |                |
|public          |sbtest1       |id             |integer      |NO             |nextval('sbtest1_id_seq'::regclass)    |                |
+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
CommSQLPlus > desc sbtest2
CommSQLPlus >
CommSQLPlus >

+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
|table_schema    |table_name    |column_name    |data_type    |is_nullable    |column_default                         |last_analyze    |
+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
|public          |sbtest2       |pad            |character    |NO             |''::bpchar                             |                |
|public          |sbtest2       |c              |character    |NO             |''::bpchar                             |                |
|public          |sbtest2       |k              |integer      |NO             |0                                      |                |
|public          |sbtest2       |id             |integer      |NO             |nextval('sbtest2_id_seq'::regclass)    |                |
+----------------+--------------+---------------+-------------+---------------+---------------------------------------+----------------+
CommSQLPlus >

 

数据样例如下：

CommSQLPlus > select k from sbtest1 limit 10;
CommSQLPlus >
select k from sbtest1 limit 10;
+----------+
|k         |
+----------+
|99857     |
|100394    |
|100853    |
|100403    |
|99963     |
|100117    |
|100275    |
|99988     |
|105454    |
|100676    |
+----------+
当前查询记录数量：10

CommSQLPlus > select k from sbtest2 limit 10;
CommSQLPlus >
select k from sbtest2 limit 10;
+----------+
|k         |
+----------+
|100979    |
|100581    |
|100424    |
|100752    |
|100996    |
|100595    |
|100167    |
|84655     |
|100245    |
|100263    |
+----------+
当前查询记录数量：10

CommSQLPlus >

分别查询如下

CommSQLPlus > select count(distinct a.k) from sbtest1 a,sbtest2 b where a.k=b.k;
CommSQLPlus >
select count(distinct a.k) from sbtest1 a,sbtest2 b where a.k=b.k;
+---------+
|count    |
+---------+
|18169    |
+---------+
当前查询记录数量：1

CommSQLPlus > select count(distinct b.k) from sbtest1 a,sbtest2 b where a.k=b.k;
CommSQLPlus >
select count(distinct b.k) from sbtest1 a,sbtest2 b where a.k=b.k;
+---------+
|count    |
+---------+
|18169    |
+---------+
当前查询记录数量：1

总数查询:

CommSQLPlus > select count(k) from sbtest1 a ;
CommSQLPlus >
select count(k) from sbtest1 a ;
+----------+
|count     |
+----------+
|200000    |
+----------+
当前查询记录数量：1

CommSQLPlus > select count(k) from sbtest2 a ;
CommSQLPlus >
select count(k) from sbtest2 a ;
+----------+
|count     |
+----------+
|200000    |
+----------+
当前查询记录数量：1

CommSQLPlus >

差异查询：为啥聚合查询总量比每个表还大

CommSQLPlus > select count(b.k) from sbtest1 a,sbtest2 b where a.k=b.k;
CommSQLPlus >
select count(b.k) from sbtest1 a,sbtest2 b where a.k=b.k;
+------------+
|count       |
+------------+
|14976808    |
+------------+
当前查询记录数量：1

CommSQLPlus >

看执行计划：还是比较懵逼的

 差异结果探索中....