Re: performance of temporary vs. regular tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




As promised, I did a tiny benchmark - basically, 8 empty tables are filled with 100k rows each within 8 transactions (somewhat typically for my application). The test machine has 4 cores, 64G RAM and RAID1 10k drives for data.

# INSERTs into a TEMPORARY table:
[joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml

real    3m18.242s
user    1m59.074s
sys     1m51.001s

# INSERTs into a standard table:
[joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml

real    3m35.090s
user    2m5.295s
sys     2m2.307s

Thus, there is a slight hit of about 10% (which may even be within meausrement variations) - your milage will vary.

Usually WAL causes a much larger performance hit than this.

Since the following command :

CREATE TABLE tmp AS SELECT n FROM generate_series(1,1000000) AS n

which inserts 1M rows takes 1.6 seconds on my desktop, your 800k rows INSERT taking more than 3 minutes is a bit suspicious unless :

- you got huge fields that need TOASTing ; in this case TOAST compression will eat a lot of CPU and you're benchmarking TOAST, not the rest of the system
- you got some non-indexed foreign key
- some other reason ?

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux