Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F2FD79FB503 for ; Sat, 2 Jun 2007 12:52:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 32730-01 for ; Sat, 2 Jun 2007 12:52:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.251]) by postgresql.org (Postfix) with ESMTP id 672279FB4FB for ; Sat, 2 Jun 2007 12:52:09 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so234190ana for ; Sat, 02 Jun 2007 08:52:08 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=JSdriMTsTzCTT10BejtLwMQLo40MoBPKmUwKxErUmEE+QGsxWMMFcsHu0e+FYRPfwppMCIE85GL8KXm0wn8du1YDh8mQ6EMOavsfXL3aBe6eQ4gAOs/vgvrF/YC8acACF0ChGtEywjHIIO810kwTDrJRXmQF/EHleMtNHyCYSgI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=mfZMbvDMcVghv59gXiV5G1iQYv7BGyXSvgcWLivKwJj8itooN51PneZk3jyTk8H3JneM9n+JEEwOlh3Cj/yufQNrwt5MDaMHktkVmtq3VHc/YZKad4UYs9AnsEzkNaUbBapsQUnT+D/zl04qEoYIUSLxTZyLH5BvO0TNKybSiHk= Received: by 10.100.11.7 with SMTP id 7mr1624649ank.1180799528639; Sat, 02 Jun 2007 08:52:08 -0700 (PDT) Received: by 10.100.152.3 with HTTP; Sat, 2 Jun 2007 08:52:08 -0700 (PDT) Message-ID: <912b58490706020852r61d308b3m595bf410d89a113@mail.gmail.com> Date: Sat, 2 Jun 2007 11:52:08 -0400 From: "Hanu Kurubar" To: "Alvaro Herrera" Subject: Re: Append table Cc: "Michal Szymanski" , "Michal Szymanski" , "Tom Lane" , pgsql-performance@postgresql.org In-Reply-To: <912b58490705302036n3c528b6fq4bfaa693a9e58dd7@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12261_12527311.1180799528598" References: <912b58490705302036n3c528b6fq4bfaa693a9e58dd7@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/15 X-Sequence-Number: 25048 ------=_Part_12261_12527311.1180799528598 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Any luck on appending two table in PostgreSQL. Below are two table with same schema that have different values. In this case EmpID is unique value. tabelA ------------ EmpId (Int) EmpName (String) 1 Hanu 2 Alvaro tabelB ------------ EmpId (Int) EmpName (String) 3 Michal 4 Tom I would be looking below output after appending tableA with tableB. Is this possible in PostgreSQL? tabelA ------------ EmpId (Int) EmpName (String) 1 Hanu 2 Alvaro 3 Michal 4 Tom Thanks, Hanu On 5/30/07, Hanu Kurubar wrote: > > Can you help me appending two table values into single table without > performing INSERT? > Note that these tables are of same schema. > > Is there any sql command is supported? > > Thanks, > Hanu > > > On 5/29/07, Alvaro Herrera wrote: > > > > Michal Szymanski wrote: > > > There is another strange thing. We have two versions of our test > > > >>environment one with production DB copy and second genereated with > > > >>minimal data set and it is odd that update presented above on copy > > of > > > >>production is executing 170ms but on small DB it executing 6s !!!! > > > > > > > >How are you vacuuming the tables? > > > > > > > Using pgAdmin (DB is installed on my laptop) and I use this tool for > > > vaccuminh, I do not think that vaccuming can help because I've tested > > on > > > both database just after importing. > > > > I think you are misunderstanding the importance of vacuuming the table. > > Try this: on a different terminal from the one running the test, run a > > VACUUM on the updated table with vacuum_cost_delay set to 20, on an > > infinite loop. Keep this running while you do your update test. Vary > > the vacuum_cost_delay and measure the average/min/max UPDATE times. > > Also try putting a short sleep on the infinite VACUUM loop and see how > > its length affects the UPDATE times. > > > > One thing not clear to me is if your table is in a clean state. Before > > running this test, do a TRUNCATE and import the data again. This will > > get rid of any dead space that may be hurting your measurements. > > > > -- > > Alvaro Herrera > > http://www.advogato.org/person/alvherre > > "The Postgresql hackers have what I call a "NASA space shot" mentality. > > Quite refreshing in a world of "weekend drag racer" developers." > > (Scott Marlowe) > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > -- > With best regards, > Hanumanthappa Kurubar > Mobile: 98 801 800 65 -- With best regards, Hanumanthappa Kurubar Mobile: 98 801 800 65 ------=_Part_12261_12527311.1180799528598 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Any luck on appending two table in PostgreSQL.
Below are two table with same schema that have different values. In this case EmpID is unique value.
 
tabelA
------------
EmpId (Int) EmpName (String)
1               Hanu
2               Alvaro
 
 
tabelB
------------
EmpId (Int) EmpName (String)
3               Michal
4               Tom
 
 
I would be looking below output after appending tableA with tableB. Is this possible in PostgreSQL?

 
tabelA
------------
EmpId (Int) EmpName (String)
1               Hanu
2               Alvaro
3               Michal
4               Tom

 

Thanks,
Hanu

 
On 5/30/07, Hanu Kurubar <hanu.kurubar@gmail.com> wrote:
Can you help me appending two table values into single table without performing INSERT?
Note that these tables are of same schema.
 
Is there any sql command is supported?
 
Thanks,
Hanu

 
On 5/29/07, Alvaro Herrera <alvherre@commandprompt.com > wrote:
Michal Szymanski wrote:
> There is another strange thing. We have two versions of our test
> >>environment one with production DB copy and second genereated with
> >>minimal data set and it is odd that update presented above on copy of
> >>production is executing 170ms but on small DB it executing 6s !!!!
> >
> >How are you vacuuming the tables?
> >
> Using pgAdmin (DB is installed on my laptop) and I use this tool for
> vaccuminh, I do not think that vaccuming can help because I've tested on
> both database just after importing.

I think you are misunderstanding the importance of vacuuming the table.
Try this: on a different terminal from the one running the test, run a
VACUUM on the updated table with vacuum_cost_delay set to 20, on an
infinite loop.  Keep this running while you do your update test.  Vary
the vacuum_cost_delay and measure the average/min/max UPDATE times.
Also try putting a short sleep on the infinite VACUUM loop and see how
its length affects the UPDATE times.

One thing not clear to me is if your table is in a clean state.  Before
running this test, do a TRUNCATE and import the data again.  This will
get rid of any dead space that may be hurting your measurements.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"The Postgresql hackers have what I call a "NASA space shot" mentality.
Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org



--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65



--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65 ------=_Part_12261_12527311.1180799528598--