Re: [PERFORM] extremly bad select performance on huge table

2014-10-23 Thread Björn Wittich
Hi Kevin, this is what I need (I think). Hopefully a cursor can operate on a join. Will read docu now. Thanks! Björn Am 22.10.2014 16:53, schrieb Kevin Grittner: Björn Wittich bjoern_witt...@gmx.de wrote: I do not want the db server to prepare the whole query result at once, my intention

Re: [PERFORM] extremly bad select performance on huge table

2014-10-22 Thread Björn Wittich
Hi Kevin, this is what I need (I think). Hopefully a cursor can operate on a join. Will read docu now. Thanks! Björn Am 22.10.2014 16:53, schrieb Kevin Grittner: Björn Wittich bjoern_witt...@gmx.de wrote: I do not want the db server to prepare the whole query result at once, my

[PERFORM] extremly bad select performance on huge table

2014-10-21 Thread Björn Wittich
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

Re: [PERFORM] extremly bad select performance on huge table

2014-10-21 Thread Björn Wittich
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

Re: [PERFORM] extremly bad select performance on huge table

2014-10-21 Thread Björn Wittich
Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive! Even a join on this table is now fast. Unfortunately, there is now another problem: The table in my example has 500 columns which I want to retrieve with

Re: [PERFORM] extremly bad select performance on huge table

2014-10-21 Thread Björn Wittich
-ow...@postgresql.org] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 3:32 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] extremly bad select performance on huge table Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced

Re: [PERFORM] query a table with lots of coulmns

2014-09-20 Thread Björn Wittich
for your help and ideas Björn Am 19.09.2014 23:40, schrieb Josh Berkus: On 09/19/2014 04:51 AM, Björn Wittich wrote: I am relatively new to postgres. I have a table with 500 coulmns and about 40 mio rows. I call this cache table where one column is a unique key (indexed) and the 499 columns

Re: [PERFORM] query a table with lots of coulmns

2014-09-20 Thread Björn Wittich
: pgsql-performance-ow...@postgresql.org [pgsql-performance-ow...@postgresql.org]quot; im Auftrag von quot;Björn Wittich [bjoern_witt...@gmx.de] Gesendet: Samstag, 20. September 2014 09:19 An: Josh Berkus; pgsql-performance@postgresql.org Betreff: Re: [PERFORM] query a table with lots of coulmns

[PERFORM] query a table with lots of coulmns

2014-09-19 Thread Björn Wittich
Hi mailing list, I am relatively new to postgres. I have a table with 500 coulmns and about 40 mio rows. I call this cache table where one column is a unique key (indexed) and the 499 columns (type integer) are some values belonging to this key. Now I have a second (temporary) table (only 2

Re: [PERFORM] query a table with lots of coulmns

2014-09-19 Thread Björn Wittich
Hi Szymon, yes I have indexes on both columns (one in each table) which I am using for join operation. Am 19.09.2014 14:04, schrieb Szymon Guz: On 19 September 2014 13:51, Björn Wittich bjoern_witt...@gmx.de mailto:bjoern_witt...@gmx.de wrote: Hi mailing list, I am relatively