代码之家 › 专栏 › 技术社区 › Dave Jarvis James Eichele

防止对具有多个where子句的查询进行全表扫描

query-optimization postgresql sql

Dave Jarvis James Eichele · 技术社区 · 14 年前

A.我已经将数据和查询移植到PostgreSQL,但是现在PostgreSQL也有同样的问题。MySQL中的解决方案是强制优化器不使用直接连接进行优化。PostgreSQL不提供这样的选项。

更新修订版

我已经隔离了查询中修复问题的部分( d.month_ref_id = 1 ):

select
  d.*
from
  daily d
join month_ref m on m.id = d.month_ref_id 
join year_ref y on y.id = m.year_ref_id
where
  m.category_id = '001' and
  d.month_ref_id = 1

然而,我不能硬编码一个月的参考 1 . A.

select
  d.*
from
  daily d
join month_ref m on m.id = d.month_ref_id 
join year_ref y on y.id = m.year_ref_id
where
  m.category_id = '001'

上的索引 daily.month_ref_id 是:

CREATE INDEX daily_month_ref_idx
  ON climate.daily
  USING btree
  (month_ref_id);

为什么查询要执行全表扫描?可以做些什么来避免它?

非常感谢。

3 回复 | 直到 14 年前

Dave Jarvis James Eichele 14 年前

尽管这可能不会对性能造成太大影响,但我还是会使用Join子句来连接表,而不是交叉连接和Where子句。
M

Select  avg(d.amount) AS amount,  y.year
From (station s
        Left Join city c -- You want to cross join on city? Why not use an Inner join?
            On c.id = 10663
                And 6371.009 
                  * SQRT( 
                        POW(RADIANS(c.latitude_decimal - s.latitude_decimal), 2) 
                        + (
                            COS(RADIANS(c.latitude_decimal + s.latitude_decimal) / 2) 
                            * POW(RADIANS(c.longitude_decimal - s.longitude_decimal), 2)
                            )
                        ) <= 50)
    Join station_district sd
        On sd.Id = s.station_district_id
    Join year_ref y
        On y.station_district_id = sd.id
    Join month_ref m
        On m.year_ref_id = y.id
    Join daily d
        On d.month_ref_id = m.id
Where s.elevation Between 0 And 2000 
    And y.year Between 1980 And 2000
    And m.month = 12
    And m.category_id = '001'
    And d.daily_flag_id <> 'M'
Group By y.year

由于您没有在结果中使用station、station\u district或city表,因此可以将这些表移到exists语句中:

Select  avg(d.amount) AS amount,  y.year
From year_ref y
    Join month_ref m
        On m.year_ref_id = y.id
    Join daily d
        On d.month_ref_id = m.id
Where y.year Between 1980 And 2000
    And m.month = 12
    And m.category_id = '001'
    And d.daily_flag_id <> 'M'
    And Exist   (
                Select 1
                From station s1
                    Join city c1
                        On c1.id = 10663
                Where 6371.009 
                      * SQRT( 
                            POW(RADIANS(c1.latitude_decimal - s1.latitude_decimal), 2) 
                            + (
                                COS(RADIANS(c1.latitude_decimal + s1.latitude_decimal) / 2) 
                                * POW(RADIANS(c1.longitude_decimal - s1.longitude_decimal), 2)
                                )
                            ) <= 50
                    And S1.station_district_id = y.station_district_id
                )
Group By y.year

mdma 14 年前

T一

免责声明:我不知道PostreSQL具体。

EDIT:这里有一个链接,描述了将WHERE子句更改为join以影响连接顺序,并讨论了join\u collapse\u限制以强制优化器使用指定的连接顺序。 http://www.postgresql.org/docs/8.2/static/explicit-joins.html

EDIT2:另一种选择是嵌套SELECT语句,这也可能迫使优化器按照指定的(反向)嵌套顺序构造查询。

Tom Drake 10 年前

select
  d.*
from month_ref m
join daily d on d.month_ref_id = m.id
join year_ref y on y.id = m.year_ref_id
where
  m.category_id = '001' and
  m.id = 1

这样,数据库就可以仅基于输入参数值轻松地定位month ref表中所有必需的行,而daily表中的行则可以使用您描述的索引轻松地定位到指定的联接上。一