postgresql - 使用 order by 时真正长时间运行的查询

Question

我的一个查询有一个主要问题:

SELECT tpostime, gispoint 
FROM mytable 
WHERE idterminal = 233463 
ORDER BY idpos DESC

当“mytable”中不存在 idterminal 时，将永远处理此查询，然后我会看到超时(具体来说是“由于用户请求而取消声明”消息)，但是当我删除订单时按条款，一切似乎都很好。现在我想知道 - idpos 是“mytable”的主键，因此它被编入索引，所以按它排序应该很快，我猜。重要的是 - “mytable”重 3gb。

表和索引定义:

CREATE TABLE mytable ( 
  idpos serial NOT NULL, 
  tpostime timestamp(0) without time zone, 
  idterminal integer DEFAULT 0, 
  gispoint geometry, 
  idtracks integer, 
  CONSTRAINT mytable_pkey PRIMARY KEY (idpos), 
  CONSTRAINT qwe FOREIGN KEY (idtracks) REFERENCES qwe (idtracks) 
    MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT abc FOREIGN KEY (idterminal) REFERENCES abc (idterminal)
    MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT enforce_geotype_gispoint 
    CHECK (geometrytype(gispoint)= 'POINT'::text OR gispoint IS NULL), 
  CONSTRAINT enforce_srid_gispoint CHECK (srid(gispoint) = 4326)
) WITH OIDS; 

CREATE INDEX idx_idterminal ON mytable USING btree (idterminal); 
CREATE INDEX idx_idtracks ON mytable USING btree (idtracks); 
CREATE INDEX idx_idtracks_idterminal ON mytable USING btree (idtracks, idterminal);

Answer 1

在我看来，idterminal 的选择性低到足以让 postgres 选择对 mytable_pkey 进行全面扫描，而不是使用 idterminal = 233463 对所有行进行排序的成本>

我建议:

CREATE INDEX idx_idterminal2 ON mytable USING btree (idterminal, idpos);

也许:

DROP INDEX idx_idterminal;

您没有提及这是否是生产数据库 - 如果是，您当然需要先在其他地方测试更改的影响。

如果您不想更改架构，您可能想尝试将优化器引入您认为最好的路径，例如(未测试)8.4 及更高版本:

SELECT * 
FROM ( SELECT tpostime, gispoint, idpos, row_number() over (order by 1)
       FROM mytable 
       WHERE idterminal = 233463 )
ORDER BY idpos DESC;

或者也许只是:

SELECT * 
FROM ( SELECT tpostime, gispoint, idpos
       FROM mytable 
       WHERE idterminal = 233463
       GROUP BY tpostime, gispoint, idpos )
ORDER BY idpos DESC;

甚至:

SELECT tpostime, gispoint 
FROM mytable 
WHERE idterminal = 233463 
ORDER BY idpos*2 DESC