Groups > comp.databases.postgresql > #382

Re: scan into many tables

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!news.musoftware.de!wum.musoftware.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From	Hans Castorp <REWYRLXHEGHO@spammotel.com>
Newsgroups	comp.databases.postgresql
Subject	Re: scan into many tables
Date	Fri, 13 Jul 2012 18:37:47 +0200
Lines	55
Message-ID	<a6b16bFpc5U1@mid.individual.net> (permalink)
References	<jtphan$934$1@dont-email.me>
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding	7bit
X-Trace	individual.net XLp/OmZorFAnBBTrXpdnJQvP8xH6MDGmSX4OKLmugsNWQu0qDFRbQKb4lu5Vo393M=
Cancel-Lock	sha1:vvKUc3+DiKLMFIAJi5h1SP/s6Lk=
User-Agent	Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To	<jtphan$934$1@dont-email.me>
Xref	csiph.com comp.databases.postgresql:382

Show key headers only | View raw

Greg Hennessy wrote on 13.07.2012 18:11:
> I have a rather large table (3 TB) that I need to split up.
> I can do a bunch of
> select yada into tmp_00 from centroids where value=0;
> select yada into tmp_01 from centroids where value=1;
> select yada into tmp_02 from centroids where value=2;
>
> but since a scan on the database takes about 40 hours,
> and I have values between 0 and 48, I'm looking at multiple
> weeks just to copy things into the smaller tables.
>
> Is there a way to do this while going through the
> database once?
>

Don't know if this is really going to be faster:

with base_data as (
    select yada, value
    from centroids
    where value in (0,1,2)
),
first_insert as (
    insert into tmp_00 (yada)
    select yada
    from base_data
    where value = 0
    returning yada
),
second_insert as (
    insert into tmp_01 (yada)
    select yada
    from base_data
    where value = 1
    returning yada
),
third_insert as (
    insert into tmp_02 (yada)
    select yada
    from base_data
    where value = 2
    returning yada
)
select count(*)
from third_insert;

If the result for the "base_data" is huge, this might not perform well.

> but since a scan on the database takes about 40 hours

That sounds like something is seriously broken in your environment. How many rows are we talking?

Another option might be a partitioned table where you simply insert everything into and the trigger takes care of distributing the rows into the individual tables.

Thread

scan into many tables Greg Hennessy <greg.hennessy@cox.net> - 2012-07-13 16:11 +0000
  Re: scan into many tables Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2012-07-13 18:37 +0200
    Re: scan into many tables Greg Hennessy <greg.hennessy@cox.net> - 2012-07-13 18:54 +0000
    Re: scan into many tables Greg Hennessy <greg.hennessy@cox.net> - 2012-07-13 20:48 +0000
  Re: scan into many tables Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-13 17:51 +0100
    Re: scan into many tables Greg Hennessy <greg.hennessy@cox.net> - 2012-07-13 19:00 +0000
      Re: scan into many tables Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-13 22:15 +0100
      Re: scan into many tables Graham Murray <newspost@gmurray.org.uk> - 2012-07-22 11:46 +0100
        Re: scan into many tables Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-22 13:09 +0100
          Re: scan into many tables Robert Klemme <shortcutter@googlemail.com> - 2012-07-22 18:55 +0200
            Re: scan into many tables Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-22 21:07 +0100
          Re: scan into many tables Don Y <this@isnotme.com> - 2012-07-22 10:30 -0700
            Re: scan into many tables Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2012-07-22 20:24 +0200
              Re: scan into many tables Don Y <this@isnotme.com> - 2012-07-22 12:54 -0700
        Re: scan into many tables Greg Hennessy <greg.hennessy@cox.net> - 2012-07-22 22:57 +0000
  Re: scan into many tables Jasen Betts <jasen@xnet.co.nz> - 2012-07-14 02:55 +0000

csiph-web