Thread: Re: [Bizgres-general] Re: faster INSERT with possible
Hannu, On 7/26/05 11:56 AM, "Hannu Krosing" <hannu@skype.net> wrote: > On T, 2005-07-26 at 11:46 -0700, Luke Lonergan wrote: > >> Yah - that's a typical approach, and it would be excellent if the COPY >> bypassed WAL for the temp table load. > > Don't *all* operations on TEMP tables bypass WAL ? Good question - do they? We had discussed the bypass as an elective option, or an automated one for special conditions (no index on table, empty table) or both. I thought that temp tables was one of those special conditions. Well - now that I test it, it appears you are correct, temp table COPY bypasses WAL - thanks for pointing it out! The following test is on a load of 200MB of table data from an ASCII file with 1 text column of size 145MB. - Luke ===================== TEST =========================== dgtestdb=# create temporary table temp1 (a text); CREATE TABLE dgtestdb=# \timing Timing is on. dgtestdb=# \i copy.ctl COPY Time: 4549.212 ms dgtestdb=# \i copy.ctl COPY Time: 3897.395 ms -- that's two tests, two loads of 200MB each, averaging 4.2 secs dgtestdb=# create table temp2 as select * from temp1; SELECT Time: 5914.803 ms -- a quick comparison to "CREATE TABLE AS SELECT", which bypasses WAL -- on bizgres dgtestdb=# drop table temp1; DROP TABLE Time: 135.782 ms dgtestdb=# drop table temp2; DROP TABLE Time: 3.707 ms dgtestdb=# create table temp1 (a text); CREATE TABLE Time: 1.667 ms dgtestdb=# \i copy.ctl COPY Time: 6034.274 ms dgtestdb=# -- This was a non-temporary table COPY, showing the slower performance of 6 secs. - Luke
Luke, > Well - now that I test it, it appears you are correct, temp table COPY > bypasses WAL - thanks for pointing it out! RIght. The problem is bypassing WAL for loading new "scratch" tables which aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since: a) Temp tables can't be shared by several writers, and b) you can't index a temp table. -- Josh Berkus Aglio Database Solutions San Francisco
On Wed, 27 Jul 2005, Josh Berkus wrote: > b) you can't index a temp table. > jurka# create temp table t (a int); CREATE jurka# create index myi on t(a); CREATE
On Wed, 2005-07-27 at 09:29 -0700, Josh Berkus wrote: > Luke, > > > Well - now that I test it, it appears you are correct, temp table COPY > > bypasses WAL - thanks for pointing it out! > > RIght. The problem is bypassing WAL for loading new "scratch" tables which > aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since: > a) Temp tables can't be shared by several writers, and > b) you can't index a temp table. The description of "scratch" tables might need some slight clarification. It kindof makes it sound like temp tables. I had in mind the extra tables that an application sometimes needs to operate faster. Denormalisations, pre-joined tables, pre-calculated results, aggregated data. These are not temporary tables, just part of the application - multi-user tables that stay across shutdown/restart. If you have gallons of GB, you will probably by looking to make use of such tables. You can use such tables for the style of ETL known as ELT, but that is not the only use. Best Regards, Simon Riggs
> I had in mind the extra tables that an application sometimes needs to
> operate faster. Denormalisations, pre-joined tables, pre-calculated
> results, aggregated data. These are not temporary tables, just part of
> the application - multi-user tables that stay across shutdown/restart.
You could also add caching search results for easy pagination without
redoing always entirely on each page the Big Slow Search Query that every
website has...
Josh Berkus <josh@agliodbs.com> writes:
> RIght. The problem is bypassing WAL for loading new "scratch" tables which
> aren't TEMPORARY tables. We need to do this for multi-threaded ETL, since:
> a) Temp tables can't be shared by several writers, and
> b) you can't index a temp table.
This may not matter given point (a), but: point (b) is completely wrong.
regards, tom lane