Re: query performance

["pepone.onrez" <pepone.onrez@xxxxxxxxx>]

Sorry Alex i forget mention that i have setscan of in my last test.

now I have set seqscan on  and indexscan on and added order by _id  

The table has an index in the _id field  

CREATE INDEX i_documentcontent_document
  ON t_documentcontent
  USING btree
  (_document);

The database was rencently vacum analyze , but not vacun full

here is the explain of 2 diferent queries , when i put a large OFFSET

 EXPLAIN ANALYZE SELECT

    t_documentcontent._id AS _id

    FROM t_documentcontent ORDER BY _id LIMIT 50 OFFSET 50000

"Limit  (cost=137068.24..137068.36 rows=50 width=58) (actual time=41119.702..41119.792
 rows=50 loops=1)"
"  ->  Sort  (cost=136943.24..137320.26 rows=150807 width=58) (actual time=41064.802..41100.424 rows=50050 loops=1)"
"        Sort Key: _id"
"        ->  Seq Scan on t_documentcontent  (cost=
0.00..110772.07 rows=150807 width=58) (actual time=106.679..33267.194 rows=150807 loops=1)"
"Total runtime: 41120.015 ms"

 EXPLAIN ANALYZE SELECT

    t_documentcontent._id AS _id

    FROM t_documentcontent ORDER BY _id LIMIT 50 OFFSET 10000

"Limit  (cost=39839.37..40038.56 rows=50 width=58) (actual time=1172.969..1194.228 rows=50 loops=1)"
"  ->  Index Scan using i_documentcontent_id on t_documentcontent  (cost=0.00..600805.54 rows=150807 width=58) (actual time=
0.077..1189.688 rows=10050 loops=1)"
"Total runtime: 1194.316 ms"

Tom
 i using uuid for the _id field that is the primary key  add a WHERE id > ?  don 't apply
the cursor aproach is also not suitable for same of my queries

I use this query for paginate contents of a filesysstem with lots of documents avoid offset is not posible always