postgresql - PostgreSQL 何时将子查询合并为连接，何时不合并？

Question

考虑以下查询:

select a.id from a
where
    a.id in (select b.a_id from b where b.x='x1' and b.y='y1') and
    a.id in (select b.a_id from b where b.x='x2' and b.y='y2')
order by a.date desc
limit 20

哪个应该可以重写为更快的那个:

select a.id from a inner join b as b1 on (a.id=b1.a_id) inner join b as b2 on (a.id=b2.a_id)
where
    b1.x='x1' and b1.y='y1' and
    b2.x='x2' and b2.y='y2'
order by a.date desc
limit 20

我们不希望通过更改我们的源代码来重写我们的查询，因为它会变得非常复杂(尤其是在使用 Django 时)。

因此，我们想知道 PostgreSQL 何时将子查询折叠为连接，何时不折叠？

这是简化的数据模型:

                                      Table "public.a"
      Column       |          Type          |                          Modifiers
-------------------+------------------------+-------------------------------------------------------------
 id                | integer                | not null default nextval('a_id_seq'::regclass)
 date              | date                   | 
 content           | character varying(256) | 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
    "a_id_date" btree (id, date)
Referenced by:
    TABLE "b" CONSTRAINT "a_id_refs_id_6e634433343d4435353" FOREIGN KEY (a_id) REFERENCES a(id) DEFERRABLE INITIALLY DEFERRED


       Table "public.b"
  Column  |   Type    | Modifiers 
----------+-----------+-----------
 a_id     | integer   | not null
 x        | text      | not null
 y        | text      | not null
Indexes:
    "b_x_y_a_id" UNIQUE CONSTRAINT, btree (x, y, a_id)
Foreign-key constraints:
    "a_id_refs_id_6e634433343d4435353" FOREIGN KEY (a_id) REFERENCES a(id) DEFERRABLE INITIALLY DEFERRED

• a 有 700 万行
• b 有 7000 万行
• b.x 的基数 = ~100
• b.y 的基数 = ~100000
• b.x 的基数，b.y = ~150000
• 设想表 c、d 和 e 具有与 b 相同的结构，并且可以额外使用以进一步减少生成的 a.ids

PostgreSQL 的版本，我们测试了查询。

• x86_64-suse-linux-gnu 上的 PostgreSQL 9.2.7，由 gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012] 编译，64 位
• x86_64-suse-linux-gnu 上的 PostgreSQL 9.4beta1，由 gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012] 编译，64 位

查询计划(具有空文件缓存和内存缓存):

• Query plans for 9.4b1 as it is more verbose than 9.2. (before UNIQUE constraint added)
• Query plans for 9.4b1 after applying the UNIQUE constraint
• Query plans for 9.4b1 after UNIQUE and statistics = 10000 to all columns of b

Answer 1

我认为，您最后的评论说明了原因:这两个查询不等价，除非引入唯一约束使它们等价。

等效架构示例:

denis=# \d a
                         Table "public.a"
 Column |  Type   |                   Modifiers                    
--------+---------+------------------------------------------------
 id     | integer | not null default nextval('a_id_seq'::regclass)
 d      | date    | not null
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "b_a_id_fkey" FOREIGN KEY (a_id) REFERENCES a(id)

denis=# \d b
       Table "public.b"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a_id   | integer | not null
 val    | integer | not null
Foreign-key constraints:
    "b_a_id_fkey" FOREIGN KEY (a_id) REFERENCES a(id)

使用该模式的等效违规数据:

denis=# select * from a order by d;
 id |     d      
----+------------
  1 | 2014-12-10
  2 | 2014-12-11
  3 | 2014-12-12
  4 | 2014-12-13
  5 | 2014-12-14
  6 | 2014-12-15
(6 rows)

denis=# select * from b order by a_id, val;
 a_id | val 
------+-----
    1 |   1
    1 |   1
    2 |   1
    2 |   1
    2 |   2
    3 |   1
    3 |   1
    3 |   2
(8 rows)

使用两个 IN 子句的行:

denis=# select a.id, a.d from a where a.id in (select b.a_id from b where b.val = 1) and a.id in (select b.a_id from b where b.val = 2) order by d;
 id |     d      
----+------------
  2 | 2014-12-11
  3 | 2014-12-12
(2 rows)

使用两个连接的行:

denis=# select a.id, a.d from a join b b1 on a.id = b1.a_id join b b2 on a.id = b2.a_id where b1.val = 1 and b2.val = 2 order by d;
 id |     d      
----+------------
  2 | 2014-12-11
  2 | 2014-12-11
  3 | 2014-12-12
  3 | 2014-12-12
(4 rows)

不过，我看到您已经对 b (a_id, x, y) 设置了唯一约束。也许在 Postgres 性能列表中突出显示问题，以了解在您的特定情况下它没有崩溃的原因——或者至少没有生成完全相同的计划。