X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 25FED475A71 for ; Thu, 20 Feb 2003 20:45:22 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 78776-07 for ; Thu, 20 Feb 2003 20:45:11 -0500 (EST) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by postgresql.org (Postfix) with ESMTP id 4C80047580B for ; Thu, 20 Feb 2003 20:45:08 -0500 (EST) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id h1L1jAW39236 for pgsql-performance@postgresql.org; Fri, 21 Feb 2003 09:45:10 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id h1L1j5739005; Fri, 21 Feb 2003 09:45:05 +0800 (WST) Message-ID: <07e301c2d94a$e6eec450$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "Manfred Koizar" , "Chantal Ackermann" Cc: "Josh Berkus" , , References: <3E50BECC.9090107@biomax.de> <3E520AD8.2000304@biomax.de> <3E5350AE.7080408@biomax.de> <94095vcb2p7db03jmbhnu1agh3k2iqrodf@4ax.com> Subject: Re: cost and actual time Date: Fri, 21 Feb 2003 09:45:24 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/161 X-Sequence-Number: 1217 I nominate Manfred for support response award of the week! Chris ----- Original Message ----- From: "Manfred Koizar" To: "Chantal Ackermann" Cc: "Josh Berkus" ; ; Sent: Thursday, February 20, 2003 6:00 PM Subject: Re: [PERFORM] cost and actual time > On Wed, 19 Feb 2003 10:38:54 +0100, Chantal Ackermann > wrote: > >Nested Loop: 53508.86 msec > >Merge Join: 113066.81 msec > >Hash Join: 439344.44 msec > > Chantal, > > you might have reached the limit of what Postgres (or any other > database?) can do for you with these data structures. Time for > something completely different: Try calculating the counts in > advance. > > CREATE TABLE occ_stat ( > did INT NOT NULL, > gid INT NOT NULL, > cnt INT NOT NULL > ) WITHOUT OIDS; > > CREATE INDEX occ_stat_dg ON occ_stat(did, gid); > CREATE INDEX occ_stat_gd ON occ_stat(gid, did); > > There is *no* UNIQUE constraint on (did, gid). You get the numbers > you're after by > SELECT did, sum(cnt) AS cnt > FROM occ_stat > WHERE gid = 'whatever' > GROUP BY did > ORDER BY cnt DESC; > > occ_stat is initially loaded by > > INSERT INTO occ_stat > SELECT did, gid, count(*) > FROM g_o INNER JOIN d_o ON (g_o.sid = d_o.sid) > GROUP BY did, gid; > > Doing it in chunks > WHERE sid BETWEEN a::bigint AND b::bigint > might be faster. > > You have to block any INSERT/UPDATE/DELETE activity on d_o and g_o > while you do the initial load. If it takes too long, see below for > how to do it in the background; hopefully the load task will catch up > some day :-) > > Keeping occ_stat current: > > CREATE RULE d_o_i AS ON INSERT > TO d_o DO ( > INSERT INTO occ_stat > SELECT NEW.did, g_o.gid, 1 > FROM g_o > WHERE g_o.sid = NEW.sid); > > CREATE RULE d_o_d AS ON DELETE > TO d_o DO ( > INSERT INTO occ_stat > SELECT OLD.did, g_o.gid, -1 > FROM g_o > WHERE g_o.sid = OLD.sid); > > On UPDATE do both. Create a set of similar rules for g_o. > > These rules will create a lot of duplicates on (did, gid) in occ_stat. > Updating existing rows and inserting only new combinations might seem > obvious, but this method has concurrency problems (cf. the thread > "Hard problem with concurrency" on -hackers). So occ_stat calls for > reorganisation from time to time: > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > CREATE TEMP TABLE t (did INT, gid INT, cnt INT) WITHOUT OIDS; > > INSERT INTO t > SELECT did, gid, sum(cnt) > FROM occ_stat > GROUP BY did, gid > HAVING count(*) > 1; > > DELETE FROM occ_stat > WHERE t.did = occ_stat.did > AND t.gid = occ_stat.gid; > > INSERT INTO occ_stat SELECT * FROM t; > > DROP TABLE t; > COMMIT; > VACUUM ANALYZE occ_stat; -- very important!! > > Now this should work, but the rules could kill INSERT/UPDATE/DELETE > performance. Depending on your rate of modifications you might be > forced to push the statistics calculation to the background. > > CREATE TABLE d_o_change ( > sid BIGINT NOT NULL, > did INT NOT NULL, > cnt INT NOT NULL > ) WITHOUT OIDS; > > ... ON INSERT TO d_o DO ( > INSERT INTO d_o_change VALUES (NEW.sid, NEW.did, 1)); > > ... ON DELETE TO d_o DO ( > INSERT INTO d_o_change VALUES (OLD.sid, OLD.did, -1)); > > ... ON UPDATE TO d_o > WHERE OLD.sid != NEW.sid OR OLD.did != NEW.did > DO both > > And the same for g_o. > > You need a task that periodically scans [dg]_o_change and does ... > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > SELECT ; > INSERT INTO occ_stat ; > DELETE ; > COMMIT; > > Don't forget to VACUUM! > > If you invest a little more work, I guess you can combine the > reorganisation into the loader task ... > > I have no idea whether this approach is better than what you have now. > With a high INSERT/UPDATE/DELETE rate it may lead to a complete > performance disaster. You have to try ... > > Servus > Manfred > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >