X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BF158474E5C for ; Thu, 20 Feb 2003 05:01:00 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 27411-04 for ; Thu, 20 Feb 2003 05:00:49 -0500 (EST) Received: from email03.aon.at (unknown [195.3.96.93]) by postgresql.org (Postfix) with SMTP id 69E14474E4F for ; Thu, 20 Feb 2003 05:00:49 -0500 (EST) Received: (qmail 105588 invoked from network); 20 Feb 2003 10:00:49 -0000 Received: from m169p001.dipool.highway.telekom.at (HELO cantor) ([62.46.11.1]) (envelope-sender ) by qmail3rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 20 Feb 2003 10:00:49 -0000 From: Manfred Koizar To: Chantal Ackermann Cc: Josh Berkus , pgsql-performance@postgresql.org, tgl@sss.pgh.pa.us Subject: Re: cost and actual time Date: Thu, 20 Feb 2003 11:00:19 +0100 Message-ID: <94095vcb2p7db03jmbhnu1agh3k2iqrodf@4ax.com> References: <3E50BECC.9090107@biomax.de> <3E520AD8.2000304@biomax.de> <3E5350AE.7080408@biomax.de> In-Reply-To: <3E5350AE.7080408@biomax.de> X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/137 X-Sequence-Number: 1193 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