-----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 1:35 PM To: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: extremly bad select performance on huge table 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 > > > > Did you check the bloat in your myprimkey index? Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
