Re: query performance - Mailing list pgsql-general
| From | Alex Turner |
|---|---|
| Subject | Re: query performance |
| Date | |
| Msg-id | 33c6269f0801132043x343ea3b5uddbe969595d11630@mail.gmail.com Whole thread Raw |
| In response to | Re: query performance (pepone.onrez <pepone.onrez@gmail.com>) |
| Responses |
Re: query performance
("Alex Turner" <armtuk@gmail.com>)
|
| List | pgsql-general |
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
Alex
On Jan 13, 2008 11:27 PM, pepone. onrez <pepone.onrez@gmail.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 SELECTFROM t_documentcontent ORDER BY _id LIMIT 50 OFFSET 50000
t_documentcontent._id AS _id
"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 SELECTFROM t_documentcontent ORDER BY _id LIMIT 50 OFFSET 10000
t_documentcontent._id AS _id
"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
pgsql-general by date: