Message-ID: <541CA2E1.4060604@agliodbs.com>
Date: Fri, 19 Sep 2014 14:40:49 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: =?UTF-8?B?QmrDtnJuIFdpdHRpY2g=?= <Bjoern_Wittich@gmx.de>,
 pgsql-performance@postgresql.org
Subject: Re: query a table with lots of coulmns
References: <541C18C5.3080204@gmx.de>
 <WM!3ed89d62d820963c8fa8b6539afa158a5ef02fb100653b75afe3203d15981cde82c644379d88b5343b51b1274dcf6c1a!@asav-3.01.com>
In-Reply-To: 
 <WM!3ed89d62d820963c8fa8b6539afa158a5ef02fb100653b75afe3203d15981cde82c644379d88b5343b51b1274dcf6c1a!@asav-3.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 09/19/2014 04:51 AM, Bj=C3=B6rn Wittich wrote:
>=20
> 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.
>=20
> Now I have a second (temporary) table (only 2 columns one is the key of
> my cache table) and I want  do an inner join between my temporary table
> and the large cache table and export all matching rows. I found out,
> that the performance increases when I limit the join to lots of small
> parts.
> But it seems that the databases needs a lot of disk io to gather all 499
> data columns.
> Is there a possibilty to tell the databases that all these colums are
> always treated as tuples and I always want to get the whole row? Perhaps
> the disk oraganization could then be optimized?

PostgreSQL is already a row store, which means by default you're getting
all of the columns, and the columns are stored physically adjacent to
each other.

If requesting only 1 or two columns is faster than requesting all of
them, that's pretty much certainly due to transmission time, not disk
IO.  Otherwise, please post your schema (well, a truncated version) and
your queries.

BTW, in cases like yours I've used a INT array instead of 500 columns to
good effect; it works slightly better with PostgreSQL's compression.

--=20
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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