From: | pepone(dot)onrez <pepone(dot)onrez(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: query performance |
Date: | 2008-01-14 04:27:31 |
Message-ID: | 198501d60801132027h1201ba2dk6f596576ae9e48df@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Turner | 2008-01-14 04:43:35 | Re: query performance |
Previous Message | Tom Lane | 2008-01-14 03:52:25 | Re: query performance |