create table as select VS create table; insert as select

Hi,

On 29 October 2010 11:46, Jacqui Caren-home <jacqui.caren@ntlworld.com> wrote:

I have inherited an application that populates a number of
temp.y tables using create table ... as select ...

What is the principle of creating this temp.y tables?
May be table partitioning is better to implement here -
http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html

This is taking roughly five to ten minutes to run

As this process hammers the database, I can only run benchmarks at night so
am asking here if anyone know if

create table ...; then insert into ... as select... ; would be faster.

or if anyone can suggest an alternative I may have missed.

I am happy to move code server side if need be.

TIA

Jacqui

I know these is not a lot of detail in the above - the system is under NDA
and I need to check with my employer before I give out any system details.

Before you ask it is not a big customer - just a very paranoid one :-)

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

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp

Jacqui Caren-home <jacqui.caren@ntlworld.com> writes:

I have inherited an application that populates a number of
temp.y tables using create table ... as select ...
As this process hammers the database, I can only run benchmarks at night so
am asking here if anyone know if
create table ...; then insert into ... as select... ; would be faster.

No, it'd likely be slower --- possibly quite a bit slower.

When you say "temp-y", do you mean these are in fact TEMP tables?
If not, can you make them be so? That would help.

regards, tom lane