sql - 存在条件时始终为假的postgres查询优化

Question

我们有一个这样的查询。

SELECT DISTINCT table_a.userid       AS userId,
                table_a.screenname   AS screenName,
FROM   table_a
       LEFT JOIN table_b
              ON ( table_a.userid = table_b.userid )
WHERE  ( table_b.organizationid IS NULL )
       AND ( table_a.companyid = '20002' )
       AND ( table_a.defaultuser = 'f' )
       AND ( ( Lower(table_a.firstname) LIKE '%7189%'
               AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.middlename) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.lastname) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.screenname) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.emailaddress) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL ) )
       AND ( table_a.status = '0' )

table_b.organizationid 有一个 not NULL 约束，所以 table_b.organizationid IS NOT NULL 等同于 1 = 2，但 postgres 仍然执行查询的剩余部分，创建临时表并在 15 秒后返回零行。

在不改变查询的情况下，是否有可能提高这个查询的性能。数据库版本是 9.1，但 9.3 也有相同的行为。

Postgres 版本:PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu，由 gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3 编译，64 位

table_a 有 230 万行table_b 有 540 万行

table_a 有 40 列(太多无法在此处列出)table_b 有 2 列

     Column     |  Type  | Modifiers
----------------+--------+-----------
 userid         | bigint | not null
 organizationid | bigint | not null
Indexes:
    "table_b_pkey" PRIMARY KEY, btree (userid, organizationid)
    "ix_7ef4ec0e" btree (organizationid)
    "ix_fb646ca6" btree (userid)

解释计划

HashAggregate  (cost=638937.42..638937.43 rows=1 width=72) (actual time=21386.436..21386.436 rows=0 loops=1)
   ->  Hash Right Join  (cost=443599.10..638937.40 rows=1 width=72) (actual time=21386.433..21386.433 rows=0 loops=1)
         Hash Cond: (table_b.userid = table_a.userid)
         Filter: (table_b.organizationid IS NULL)
         ->  Seq Scan on table_b  (cost=0.00..95488.04 rows=6020704 width=16) (actual time=0.009..4158.880 rows=5497919 loops=1)
         ->  Hash  (cost=441059.52..441059.52 rows=104846 width=72) (actual time=12356.795..12356.795 rows=215 loops=1)
               Buckets: 16384  Batches: 2  Memory Usage: 12kB
               ->  Seq Scan on table_a  (cost=0.00..441059.52 rows=104846 width=72) (actual time=43.250..12355.735 rows=215 loops=1)
                     Filter: ((NOT defaultuser) AND (companyid = 20002::bigint) AND (status = 0) AND ((lower((firstname)::text) ~~ '%7189%'::text) OR (lower((middlename)::text) ~~ '%7189%'::text) OR (lower((lastname)::text) ~~ '%7189%'::text) OR (lower((screenname)::text) ~~ '%7189%'::text) OR (lower((emailaddress)::text) ~~ '%7189%'::text)))
 Total runtime: 21386.608 ms
(10 rows)

-- 萨米尔

Answer 1

我知道您不想重写查询。但是查询重写对规划者有很大帮助。我会把这个查询写成:

SELECT DISTINCT table_a.userid       AS userId,
                table_a.screenname   AS screenName,
FROM   table_a
LEFT JOIN
(select userid from --i think only userid is needed in this query
   table_b  table_b.organizationid IS NULL ) as table_b
              ON ( table_a.userid = table_b.userid )
WHERE  
       ( table_a.companyid = '20002' )
       AND ( table_a.defaultuser = 'f' )
       AND ( ( Lower(table_a.firstname) LIKE '%7189%'
               AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.middlename) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.lastname) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.screenname) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL )
              OR ( Lower(table_a.emailaddress) LIKE '%7189%'
                   AND '%7189%' IS NOT NULL ) )
       AND ( table_a.status = '0' )