=?ISO-8859-15?Q?Bj=F6rn_Wittich?= <Bjoern_Wittich@xxxxxx> writes: > Here is the explain (analyze,buffers) select mycolumn from myhugetable > "Index Only Scan using myprimkey on myhugetable (cost=0.00..8224444.82 > rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 > loops=1)" > " Heap Fetches: 356861" > " Buffers: shared hit=71799472 read=613813" > "Total runtime: 2503009.611 ms" So that works out to about 4 msec per page fetched considering only I/O costs, which is about as good as you're likely to get if the data is sitting on spinning rust. You could potentially make it faster with a VACUUM (to mark all pages all-visible and eliminate the "heap fetches" costs), or a REINDEX (so that the index scan becomes more nearly sequential instead of random access). However, unless the data is nearly static those will just be temporary fixes: the time will degrade again as you update the table. > Note: This select is just for testing. My final statement will be a join > on this table via the "mycolumn" column. In that case it's probably a waste of time to worry about the performance of this query as such. In the first place, a join is not likely to use the index at all unless it's fetching a relatively small number of rows, and in the second place it seems unlikely that the join query can use an IndexOnlyScan on this index --- I imagine that the purpose of the join will require fetching additional columns. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
