代码之家 › 专栏 › 技术社区 › YogeshR

索引存在,但排序仍然需要时间

postgresql-9.6 indexing performance postgresql

YogeshR · 技术社区 · 7 年前

我正在处理一个已在我们的一项服务中使用的查询。以下是查询:

select  rg.reservationGuestId, rg.reservationId, rg.checkindate,
        rg.checkoutdate, rg.assignedroom , tr.orderDate, tr.assignedDate,
        cast(tr.orderId as char(36)) , g.titleCode, g.firstName,
        g.middleName, g.lastName, g.birthDate, g.genderCode, g.email,
        g.preferredLanguage , cast(a.addressId as char(36)), a.addressTypeCode,
        a.line1, a.line2, a.city, a.state, a.countryCode as aCountryCode,
        a.zip , cast(p.phoneId as char(36)), p.phoneTypeCode,
        p.countryCode as pCountryCode, p.areaCode, p.number
    from  reservationguest rg
    inner join  guest g  ON rg.guestid = g.guestid
    inner join  address a  ON g.guestid = a.guestid
    inner join  phone p  ON g.guestid = p.guestid
    left join  orderdetail tr  ON rg.reservationguestid = tr.reservationguestid
    where  ((0 = 0)
              or  (rg.reservationGuestId in (null))
           )
      and  (('2019-05-01' = 'null')
              or  (rg.checkindate >= '2019-05-01')
           )
      and  (('2019-09-08' = 'null')
              or  (rg.checkindate <= '2019-09-08')
           )
      and  ((1 = 0)
              or  (a.addressTypeCode in ('SHIPPING'))
           )
      and  ((1 = 0)
              or  (p.phoneTypeCode in ('HOME'))
           )
      and  (('' = 'null')
              or  (('' = '')
                      and  (tr.orderDate is null)
                  )
              or  (tr.orderDate = '2019-06-02 00:00:00')
           )
    order by  rg.checkindate, rg.lastmodifieddate

上述查询需要将近1150毫秒才能获取161500条记录。

以下是此查询的执行计划:

Sort  (cost=21727.93..21732.95 rows=2009 width=683) (actual time=928.206..1117.145 rows=161500 loops=1)
  Sort Key: rg.checkindate, rg.lastmodifieddate
  Sort Method: external merge  Disk: 55936kB
  ->  Hash Right Join  (cost=15262.53..21617.71 rows=2009 width=683) (actual time=267.553..576.902 rows=161500 loops=1)
        Hash Cond: ((tr.reservationguestid)::text = (rg.reservationguestid)::text)
        Filter: ((tr.orderdate IS NULL) OR (tr.orderdate = '2019-06-02 00:00:00'::timestamp without time zone))
        Rows Removed by Filter: 252112
        ->  Seq Scan on orderdetail tr  (cost=0.00..6047.00 rows=66800 width=69) (actual time=0.018..36.367 rows=66887 loops=1)
        ->  Hash  (cost=15210.52..15210.52 rows=4161 width=255) (actual time=266.789..266.789 rows=18521 loops=1)
              Buckets: 16384 (originally 8192)  Batches: 2 (originally 1)  Memory Usage: 3969kB
              ->  Nested Loop  (cost=5302.72..15210.52 rows=4161 width=255) (actual time=62.445..248.868 rows=18521 loops=1)
                    ->  Hash Join  (cost=5302.30..6748.12 rows=3322 width=258) (actual time=62.378..83.816 rows=6762 loops=1)
                          Hash Cond: ((p.guestid)::text = (g.guestid)::text)
                          ->  Bitmap Heap Scan on phone p  (cost=263.88..1624.42 rows=13883 width=70) (actual time=1.482..13.057 rows=13909 loops=1)
                                Recheck Cond: ((phonetypecode)::text = 'HOME'::text)
                                Heap Blocks: exact=1186
                                ->  Bitmap Index Scan on ix_phone_phonetypecode  (cost=0.00..260.41 rows=13883 width=0) (actual time=1.315..1.315 rows=13909 loops=1)
                                      Index Cond: ((phonetypecode)::text = 'HOME'::text)
                          ->  Hash  (cost=4952.89..4952.89 rows=6842 width=188) (actual time=60.860..60.860 rows=6811 loops=1)
                                Buckets: 8192  Batches: 1  Memory Usage: 1664kB
                                ->  Hash Join  (cost=1774.72..4952.89 rows=6842 width=188) (actual time=28.954..56.879 rows=6811 loops=1)
                                      Hash Cond: ((a.guestid)::text = (g.guestid)::text)
                                      ->  Bitmap Heap Scan on address a  (cost=137.45..3221.97 rows=6842 width=100) (actual time=1.174..22.254 rows=6811 loops=1)
                                            Recheck Cond: ((addresstypecode)::text = 'SHIPPING'::text)
                                            Heap Blocks: exact=2290
                                            ->  Bitmap Index Scan on ix_address_addresstypecode  (cost=0.00..135.73 rows=6842 width=0) (actual time=0.877..0.877 rows=6811 loops=1)
                                                  Index Cond: ((addresstypecode)::text = 'SHIPPING'::text)
                                      ->  Hash  (cost=1279.90..1279.90 rows=28590 width=88) (actual time=27.704..27.704 rows=28590 loops=1)
                                            Buckets: 32768  Batches: 1  Memory Usage: 3708kB
                                            ->  Seq Scan on guest g  (cost=0.00..1279.90 rows=28590 width=88) (actual time=0.015..17.576 rows=28590 loops=1)
                    ->  Index Scan using ix_reservationguest_guestid_checkindate_lastmodifieddate on reservationguest rg  (cost=0.42..2.53 rows=2 width=129) (actual time=0.015..0.023 rows=3 loops=6762)
                          Index Cond: (((guestid)::text = (g.guestid)::text) AND (checkindate >= '2019-05-01'::date) AND (checkindate <= '2019-09-08'::date))
Planning time: 3.343 ms
Execution time: 1173.074 ms

我认为这个查询非常优化。然而,在我移除 order by 子句中,只需大约550毫秒,几乎占总时间的一半。

由于我对PostgreSQL的内部结构知之甚少,所以我不确定如何使用索引进行排序 ix_reservationguest_guestid_checkindate_lastmodifieddate 因为在执行计划中,排序操作没有提到索引名。

问题

如果它确实使用索引进行排序,那么这是我能从Postgres获得的最短执行时间吗?
如果它不使用索引,那么有没有办法提高排序性能?
另外,我应该创建什么索引来避免对 orderdetail 桌子

0 回复 | 直到 7 年前

Rick James diyism 7 年前

我懂了 'null' 和 null .你用对了吗?

做 in (null) 按预期工作?

考虑在这样的情况下清理查询:

and   (('2019-05-01' = 'null')
   or  (rg.checkindate >= '2019-05-01')
      )

cast(p.phoneId as char(36)) --这是一个过度规范化的例子。这个 phoneID 比价值还大!

客户将如何处理“161500条记录”?我希望你不会在用户界面上向某些人展示这一点!考虑总结数据,而不是抛弃它。