Index基本查询中不使用

Question

有表块:

CREATE TABLE IF NOT EXISTS "block" (
"hash" char(66) CONSTRAINT block_pk PRIMARY KEY,
"size" text,
"miner" text ,
"nonce" text,
"number" text,
"number_int" integer not null,
"gasused" text ,
"mixhash" text ,
"gaslimit" text ,
"extradata" text ,
"logsbloom" text,
"stateroot" char(66) ,
"timestamp" text ,
"difficulty" text ,
"parenthash" char(66) ,
"sha3uncles" char(66) ,
"receiptsroot" char(66),
"totaldifficulty" text ,
"transactionsroot" char(66)
);
CREATE INDEX number_int_index ON block (number_int);

表大约有3M行，当查询一个简单的查询结果是:

EXPLAIN ANALYZE select number_int from block where number_int > 1999999 and number_int < 2999999 order by number_int desc limit 1;
QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
Limit  (cost=110.00..110.00 rows=1 width=4) (actual time=16154.891..16154.894 rows=1 loops=1)
->  Sort  (cost=110.00..112.50 rows=1000 width=4) (actual time=16154.890..16154.890 rows=1 loops=1)
Sort Key: number_int DESC
Sort Method: top-N heapsort  Memory: 25kB
->  Seq Scan on block  (cost=0.00..105.00 rows=1000 width=4) (actual time=172.766..16126.135 rows=190186 loops=1)
Remote Filter: ((number_int > 1999999) AND (number_int < 2999999))
Planning Time: 19.961 ms
Execution Time: 16155.382 ms
Peak Memory Usage: 1113 kB
(9 rows)

任何建议吗?关于

我尝试了一些我在stackoverflow中发现的东西，结果相同

select number_int from block where number_int > 1999999 and number_int < 2999999 order by number_int+0 desc limit 1;

Answer 1

嗨，这个问题与yugabyte有关，索引或其他与postgres相关的东西没有问题，我最终迁移到一个自我管理的数据库，但至少yugabyte与postgres完全兼容，因为我迁移到pg_dump没有任何问题。如果您不想管理数据库服务器，那么在您开始时，这是值得的。