Re: query performance

If you have to access the data this way (with no where clause at all - which
sometimes you do) then I have already provided a solution that will work
reasonably well. If you create what is essentially a materialized view of
just the id field, the sequence scan will return much fewer pages than when
you do it on the main table. Then you join it to the indexed main table,
and page in just the rows you need. Voila - much faster result. Of course
we haven't really talked about how that will affect insert speed and delete
speed if you trigger then up, but you haven't really talked about any
requirements there.

Alex

On Jan 13, 2008 11:27 PM, pepone. onrez <pepone(dot)onrez(at)gmail(dot)com> wrote:

> 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.07rows=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.688rows=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