2

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;
Brahian
  • 46
  • 1
  • 5
  • I think that [ANALYZE](https://www.postgresql.org/docs/9.3/sql-analyze.html) can make a difference ? (Do `ANALYZE block`, and try your query again) – Luuk Nov 14 '22 at 19:15
  • rows=1000, that looks like an almost empty table (to the database). When did (auto) analyze run its last job? – Frank Heikens Nov 14 '22 at 19:17
  • 1) Lets hope you are not actually using Postgres 9.3 as it is 4 years past EOL. If you are not then a) get rid of the postgresql-9.3 tag. b) indicate what version you are using. 2) Have you run `ANALYZE` on `block`? **Add information as update to question**. – Adrian Klaver Nov 14 '22 at 19:17
  • 2
    That is not PostgreSQL. PostgreSQL has no "peak memory usage" or "remote filter". Please tag your database system correctly. – Laurenz Albe Nov 14 '22 at 19:22
  • sorry, its yugabyte sql database compatible with posgres, I updated – Brahian Nov 14 '22 at 19:32
  • This is just a yugabyte problem, PostgreSQL has no problem getting this right. The thing you found on stackoverflow disables the index from being used for ordering, which is the opposite of what you want. – jjanes Nov 14 '22 at 20:28

1 Answers1

0

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.

Brahian
  • 46
  • 1
  • 5