database-indexesyugabytedb

Index Not use in basic query


Having the table block:

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);

The table has about 3M of rows , when a query a simple query the results are:

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)

any advice? Regards

I tried something I've found here in stackoverflow with the same result

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

Solution

  • Hi the problem was related to yugabyte, there was not a issue with a index or with other stuff related with postgres, I ended up migrated to a self-managed database, but at least yugabyte is fully compatible with postgres because I migrated with pg_dump without any problem. It worth it when you are starting if you don't want to manage the database server.