From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?ISO-8859-15?Q?Bj=F6rn_Wittich?= <Bjoern_Wittich@gmx.de>
cc: pgsql-performance@postgresql.org
Subject: Re: extremly bad select performance on huge table
In-reply-to: <5446972B.9040409@gmx.de>
References: <5446972B.9040409@gmx.de>
Comments: In-reply-to =?ISO-8859-15?Q?Bj=F6rn_Wittich?=
 <Bjoern_Wittich@gmx.de>
 message dated "Tue, 21 Oct 2014 19:26:03 +0200"
Date: Tue, 21 Oct 2014 15:07:42 -0400
Message-ID: <9706.1413918462@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

=?ISO-8859-15?Q?Bj=F6rn_Wittich?= <Bjoern_Wittich@gmx.de> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance