pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Append table
6+ messages / 4 participants
[nested] [flat]
* Re: Append table
@ 2007-05-31 03:36 Hanu Kurubar <hanu.kurubar@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Hanu Kurubar @ 2007-05-31 03:36 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Michal Szymanski <szymanskim@gdfi.pl>; Michal Szymanski <szymanskim@datera.pl>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Append table
@ 2007-06-02 15:52 Hanu Kurubar <hanu.kurubar@gmail.com>
parent: Hanu Kurubar <hanu.kurubar@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Hanu Kurubar @ 2007-06-02 15:52 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Michal Szymanski <szymanskim@gdfi.pl>; Michal Szymanski <szymanskim@datera.pl>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Append table
@ 2007-06-02 16:04 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Hanu Kurubar <hanu.kurubar@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Arjen van der Meijden @ 2007-06-02 16:04 UTC (permalink / raw)
To: Hanu Kurubar <hanu.kurubar@gmail.com>; +Cc: pgsql-performance
There are two solutions:
You can insert all data from tableB in tableA using a simple insert
select-statement like so:
INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;
Or you can visually combine them without actually putting the records in
a single table. That can be with a normal select-union statement or with
a view, something like this:
SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;
You can use this query as a table-generating subquery in a FROM-clause,
like so:
SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName
FROM tabelB) as emps WHERE EmpId = 1;
Or with the view:
CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID,
EmpName FROM tabelB;
And then you can use the view as if it was a normal table (altough
inserts are not possible without applying rules to them, see the manual
for that).
SELECT * FROM tabelC WHERE EmpId = 1;
Best regards,
Arjen
On 2-6-2007 17:52 Hanu Kurubar wrote:
> 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
> <mailto: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
> <mailto: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
> <http://archives.postgresql.org/;
>
>
>
>
> --
> With best regards,
> Hanumanthappa Kurubar
> Mobile: 98 801 800 65
>
>
>
>
> --
> With best regards,
> Hanumanthappa Kurubar
> Mobile: 98 801 800 65
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Append table
@ 2007-06-02 16:36 Gregory Stark <stark@enterprisedb.com>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
2 siblings, 0 replies; 6+ messages in thread
From: Gregory Stark @ 2007-06-02 16:36 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: Hanu Kurubar <hanu.kurubar@gmail.com>; pgsql-performance
"Arjen van der Meijden" <acmmailing@tweakers.net> writes:
> There are two solutions:
...
> Or you can visually combine them without actually putting the records in a
> single table. That can be with a normal select-union statement or with a view,
> something like this:
> SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;
If you're sure the two sets are distinct or you want to get any duplicates and
not eliminate them then if you went with this option you would want to use
"UNION ALL" not just a plain union.
In SQL UNION has to remove duplicates which often involves gathering all the
records and performing a big sort and lots of extra work. UNION ALL is much
faster and can start returning records right away.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Append table
@ 2007-06-02 19:49 Hanu Kurubar <hanu.kurubar@gmail.com>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
2 siblings, 0 replies; 6+ messages in thread
From: Hanu Kurubar @ 2007-06-02 19:49 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: pgsql-performance
Thanks for quick answer.
Previsoly I have exported table records into employee.csv file using COPY
command which has 36,00,0000 records.
After that I have added few more entries in database and EmpId is
incremented.
I want put the exported data back into database with re-generating new
EmpId. Like importing back all data without harming existing data.
If I choose INSERT opeartion, it is very time consuming.
I am thinking of creating new table (dummy table) and copying all data (COPY
from command) into that table and maniplate the data so that EmpId is unique
in parent table and dummy table and then append these two tables.
I feel creating views and joins will make things complex.
Do you have inputs on this?
On 6/2/07, Arjen van der Meijden <acmmailing@tweakers.net> wrote:
>
> There are two solutions:
> You can insert all data from tableB in tableA using a simple insert
> select-statement like so:
> INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;
>
> Or you can visually combine them without actually putting the records in
> a single table. That can be with a normal select-union statement or with
> a view, something like this:
> SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;
>
> You can use this query as a table-generating subquery in a FROM-clause,
> like so:
>
> SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName
> FROM tabelB) as emps WHERE EmpId = 1;
>
> Or with the view:
> CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID,
> EmpName FROM tabelB;
>
> And then you can use the view as if it was a normal table (altough
> inserts are not possible without applying rules to them, see the manual
> for that).
>
> SELECT * FROM tabelC WHERE EmpId = 1;
>
> Best regards,
>
> Arjen
>
> On 2-6-2007 17:52 Hanu Kurubar wrote:
> > 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
> > <mailto: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
> > <mailto: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
> > <http://archives.postgresql.org/;
> >
> >
> >
> >
> > --
> > With best regards,
> > Hanumanthappa Kurubar
> > Mobile: 98 801 800 65
> >
> >
> >
> >
> > --
> > With best regards,
> > Hanumanthappa Kurubar
> > Mobile: 98 801 800 65
>
--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Append table
@ 2007-06-05 20:11 Chander Ganesan <chander@otg-nc.com>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
2 siblings, 0 replies; 6+ messages in thread
From: Chander Ganesan @ 2007-06-05 20:11 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: Hanu Kurubar <hanu.kurubar@gmail.com>; pgsql-performance
Arjen van der Meijden wrote:
> There are two solutions:
> You can insert all data from tableB in tableA using a simple insert
> select-statement like so:
> INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;
>
> Or you can visually combine them without actually putting the records
> in a single table. That can be with a normal select-union statement or
> with a view, something like this:
> SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;
Since they both have the same schema, you could also combine them by
creating a parent table and making both tables children. Check out
PostgreSQL's inheritance features. To make an existing table a child
you'll need to be using PostgreSQL 8.2 or newer.
create table emp_rollup (like tabelA);
alter table tabelA inherits emp_rollup;
alter table tabelB inherits emp_rollup;
Now issue your queries against emp_rollup... You could also just make
tabelB a child of tabelA:
alter table tabelB inherits tabelA;
But that would mean that if you wanted to query only tabelA you'd have
to modify your query syntax.
select * from ONLY tabelA;
Would only retrieve records from tabelA ...
You could also allow PostgreSQL to limit its index usage based on the
EmpID field by defining some table constraints and enabling constraint
exclusion.
>
> You can use this query as a table-generating subquery in a
> FROM-clause, like so:
>
> SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName
> FROM tabelB) as emps WHERE EmpId = 1;
>
> Or with the view:
> CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID,
> EmpName FROM tabelB;
>
> And then you can use the view as if it was a normal table (altough
> inserts are not possible without applying rules to them, see the
> manual for that).
>
> SELECT * FROM tabelC WHERE EmpId = 1;
>
> Best regards,
>
> Arjen
>
> On 2-6-2007 17:52 Hanu Kurubar wrote:
>> 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
>>
--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Expert PostgreSQL Training - http://test.otg-nc.com/training-courses/coursedetail.php?courseid=40&cat_id=8
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-05 20:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-31 03:36 Re: Append table Hanu Kurubar <hanu.kurubar@gmail.com>
2007-06-02 15:52 ` Hanu Kurubar <hanu.kurubar@gmail.com>
2007-06-02 16:04 ` Arjen van der Meijden <acmmailing@tweakers.net>
2007-06-02 16:36 ` Gregory Stark <stark@enterprisedb.com>
2007-06-02 19:49 ` Hanu Kurubar <hanu.kurubar@gmail.com>
2007-06-05 20:11 ` Chander Ganesan <chander@otg-nc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox