Message-ID: <54469947.1080006@gmx.de>
Date: Tue, 21 Oct 2014 19:35:03 +0200
From: =?ISO-8859-15?Q?Bj=F6rn_Wittich?= <Bjoern_Wittich@gmx.de>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: extremly bad select performance on huge table
References: <5446972B.9040409@gmx.de>
In-Reply-To: <5446972B.9040409@gmx.de>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Sorry forget to copy the buffer information:

"  Heap Fetches: 356861"

"  Buffers: shared hit=71799472 read=613813"


> Hi newsgroup,
>
> I have a very huge table (70 mio rows ) with a key (text length about 
> 30 characters each key). A select on this indexed column "myprimkey" 
> (index on column mycolumn)  took more than 30 mins.
>
> 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"
>
> "Total runtime: 2503009.611 ms"
>
>
> Even repeating the query does not show a performance improvement. I 
> assume that the index itself is too large for my db cache. What can I 
> do to gain performance? Which parameters can I adapt? Having a huge 
> Linux machine with 72 GB RAM.
>
> Note: This select is just for testing. My final statement will be a 
> join on this table via the "mycolumn" column.
>
> Thanks for your help
> Björn
>
>
>
>


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance