Message-ID: <541C2603.5060203@gmx.de>
Date: Fri, 19 Sep 2014 14:48:03 +0200
From: =?UTF-8?B?QmrDtnJuIFdpdHRpY2g=?= <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: Szymon Guz <mabewlun@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: query a table with lots of coulmns
References: <541C18C5.3080204@gmx.de>
 <CAFjNrYsePAgOqjPvnhZauxpL7qQ1vcMEkEgvt1hE8PS2PCN1FQ@mail.gmail.com>
In-Reply-To: 
 <CAFjNrYsePAgOqjPvnhZauxpL7qQ1vcMEkEgvt1hE8PS2PCN1FQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------060207040102000808040502"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------060207040102000808040502
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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_Wittich@gmx.de 
> <mailto:Bjoern_Wittich@gmx.de>> wrote:
>
>     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 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?
>
> Hi,
> do you have indexes on the columns you use for joins?
>
> Szymon


--------------060207040102000808040502
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">Hi Szymon,<br>
      <br>
      yes I have indexes on both columns (one in each table) which I am
      using for join operation.<br>
      <br>
      Am 19.09.2014 14:04, schrieb Szymon Guz:<br>
    </div>
    <blockquote
cite="mid:CAFjNrYsePAgOqjPvnhZauxpL7qQ1vcMEkEgvt1hE8PS2PCN1FQ@mail.gmail.com"
      type="cite">
      <div dir="ltr"><br>
        <div class="gmail_extra"><br>
          <div class="gmail_quote">On 19 September 2014 13:51, Björn
            Wittich <span dir="ltr">&lt;<a moz-do-not-send="true"
                href="mailto:Bjoern_Wittich@gmx.de" target="_blank">Bjoern_Wittich@gmx.de</a>&gt;</span>
            wrote:<br>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi
              mailing list,<br>
              <br>
              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.<br>
              <br>
              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.<br>
              But it seems that the databases needs a lot of disk io to
              gather all 499 data columns.<br>
              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?<br>
              <br clear="all">
            </blockquote>
            <div> </div>
            <div>Hi,<br>
              do you have indexes on the columns you use for joins? <br>
              <br>
            </div>
            <div>Szymon<br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------060207040102000808040502--