From: PFC <lists@peufeu.com>
To: Alexy Khrabrov <deliverable@gmail.com>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
Date: Fri, 02 May 2008 23:30:44 +0200
Message-ID: <op.uajp1iotcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>
<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>
<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
<dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>
<21403.1209759217@sss.pgh.pa.us>
<72E02D29-848B-467A-AE6B-401568010254@gmail.com>
<dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
<481B8160.2090106@emolecules.com>
<C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>
> I created several indices for the primary table, yes. Sure I can do a
> table for a volatile column, but then I'll have to create a new such
> table for each derived column -- that's why I tried to add a column to
> the existing table. Yet seeing this is really slow, and I need to to
> many derived analyses like this -- which are later scanned in other
> computations, so should persist -- I indeed see no other way but to
> procreate derived tables with the same key, one column per each...
OK, so in that case, if you could do all of your derived column
calculations in one query like this :
CREATE TABLE derived AS SELECT ... FROM ... (perform all your derived
calculations here)
or :
BEGIN; <-- this is important to avoid writing xlog
CREATE TABLE derived AS ...
INSERT INTO derived SELECT ... FROM ... (perform all your derived
calculations here)
COMMIT;
Basically, updating the entire table several times to add a few simple
columns is a bad idea. If you can compute all the data you need in one
query, like above, it will be much faster. Especially if you join one
large table to several smaller ones, and as long as the huge data set
doesn't need to be sorted (check the query plan using EXPLAIN). Try to do
as much as possible in one query to scan the large dataset only once.
Note that the above will be faster than updating the entire table since
it needs to write much less data : it doesn't need to delete the old rows,
and it doesn't need to write the transaction log, since if the transaction
rolls back, the table never existed anyway. Also since your newly created
table doesn't have any indexes, they won't need to be updated.
If you really need to update an entire table multiple times, you will
need to :
- Use hardware that can handle disk writes at a decent speed (that isn't
a characteristic of a laptop drive)
- use MyIsam, yes (but if you need to make complex queries on the data
afterwards, it could suck).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, deliverable@gmail.com, craig_james@emolecules.com
Subject: Re: two memory-consuming postgres processes
In-Reply-To: <op.uajp1iotcigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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