Postgres指数扫描向前与向后=357 X的速度差慢

Question

PostgreSQL 9.1.2在x86_64-未知-linux-gnu上，由gcc (GCC) 4.1.2 20080704 (RedHat4.1.2-51)，64位编译

专用数据库服务器

• 4GB内存
• Shared_Buffers =1GB
• Effective_cache_size = 3GB
• Work_mem =32

分析完成

查询多次运行，相同的差异/结果

默认统计= 1000

查询(5366ms)：

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange || + E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange asc
limit 15

慢升解释分析：

http://explain.depesz.com/s/zFz

查询(15)：

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange ||+ E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange desc limit 15

快速下降解释分析：

http://explain.depesz.com/s/OP7

索引: changes_shareschange是使用默认升序创建的btree索引。is索引大小为32 is。

查询计划和估计完全相同，只不过desc对changes_shareschange进行了反向索引扫描，而不是索引扫描。

然而，对于升序版本，实际运行时性能要慢357倍，而不是下降。

为什么我要怎么修理它？

Answer 1

由于我喜欢用老式的自联接而不存在子句替换聚合函数，所以我的尝试如下：

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;


CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;