From drlaz@attglobal.net Wed Feb 18 22:53:23 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 99829D1D1A4 for ; Wed, 18 Feb 2004 22:53:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 23927-09 for ; Wed, 18 Feb 2004 18:53:23 -0400 (AST) Received: from smtp811.mail.sc5.yahoo.com (smtp811.mail.sc5.yahoo.com [66.163.170.81]) by svr1.postgresql.org (Postfix) with SMTP id AB24FD1E0F4 for ; Wed, 18 Feb 2004 18:53:19 -0400 (AST) Received: from unknown (HELO ANDYXP) (drlaz@sbcglobal.net@64.160.53.113 with login) by smtp811.mail.sc5.yahoo.com with SMTP; 18 Feb 2004 22:52:18 -0000 Message-ID: <0a0c01c3f671$ceb08c80$847ba8c0@ANDYXP> From: "Andrew Lazarus" To: Subject: JOIN order, 15K, 15K, 7MM rows Date: Wed, 18 Feb 2004 14:51:52 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0A09_01C3F62E.BE85BCB0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=BAYES_44, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200402/245 X-Sequence-Number: 5769 This is a multi-part message in MIME format. ------=_NextPart_000_0A09_01C3F62E.BE85BCB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable All three tables have the same integer key, and it's indexed. I parenthesized the joins to do the two small tables first. I'm running and INSERT INTO ... SELECT query with this join (one record add= ed per record in join), 4 hours down and all I have to show for it is 100 r= ecycled transaction logs. ? If it ever returns, I can post the Explain output. ------=_NextPart_000_0A09_01C3F62E.BE85BCB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
All three tables have the same int= eger key,=20 and it's indexed.
 
I parenthesized the joins to do th= e two=20 small tables first.
 
I'm running and INSERT INTO ... SE= LECT=20 query with this join (one record added per record in join), 4 hours down an= d all=20 I have to show for it is 100 recycled transaction logs. ?
 
If it ever returns, I can post the= Explain=20 output.
------=_NextPart_000_0A09_01C3F62E.BE85BCB0-- From josh@agliodbs.com Sun Feb 22 19:02:55 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9F602D1B9EB for ; Sun, 22 Feb 2004 19:02:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 34886-10 for ; Sun, 22 Feb 2004 15:02:53 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 7E6BDD1B9BD for ; Sun, 22 Feb 2004 15:02:51 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4474990; Sun, 22 Feb 2004 11:03:58 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Andrew Lazarus" , Subject: Re: JOIN order, 15K, 15K, 7MM rows Date: Sun, 22 Feb 2004 11:00:40 -0800 User-Agent: KMail/1.4.3 References: <0a0c01c3f671$ceb08c80$847ba8c0@ANDYXP> In-Reply-To: <0a0c01c3f671$ceb08c80$847ba8c0@ANDYXP> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200402221100.40176.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/249 X-Sequence-Number: 5773 Andrew, > I'm running and INSERT INTO ... SELECT query with this join (one record > added per record in join), 4 hours down and all I have to show for it is > 100 recycled transaction logs. ? > > If it ever returns, I can post the Explain output. How about giving us the query and the regular EXPLAIN output right now? I've a feeling that you have an unconstrained join in the query somewhere. -- Josh Berkus Aglio Database Solutions San Francisco From andrew@pillette.com Mon Feb 23 20:01:21 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5EF30D1D26A for ; Mon, 23 Feb 2004 20:01:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 35072-01 for ; Mon, 23 Feb 2004 16:01:17 -0400 (AST) Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by svr1.postgresql.org (Postfix) with ESMTP id 0876CD1B973 for ; Mon, 23 Feb 2004 16:01:08 -0400 (AST) Received: (from andrew@localhost) by pillette.com (8.11.6/8.11.6) id i1NK0rQ30781; Mon, 23 Feb 2004 12:00:53 -0800 Date: Mon, 23 Feb 2004 12:00:53 -0800 From: andrew@pillette.com Message-Id: <200402232000.i1NK0rQ30781@pillette.com> Subject: Re: JOIN order, 15K, 15K, 7MM rows To: pgsql-performance@postgresql.org Cc: X-Originating-IP: 192.168.1.203 X-Mailer: Webmin 0.940 MIME-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org Content-Transfer-Encoding: 7bit Content-Type: text/plain X-Archive-Number: 200402/327 X-Sequence-Number: 5851 EXPLAIN INSERT INTO public.historical_price ( security_serial_id, [7 fields of proprietary data]) SELECT public.security_series.security_serial_id, [7 fields of data], FROM obsolete.datadb_fix INNER JOIN (obsolete.calcdb INNER JOIN public.security_series ON obsolete.calcdb.serial=public.security_series.legacy_calcdb_id) ON obsolete.datadb_fix.id=public.security_series.legacy_calcdb_id; datadb_fix is about 5.5MM records. The other two tables are about 15K records. Hash Join (cost=1151.63..225863.54 rows=5535794 width=53) Hash Cond: ("outer".id = "inner".serial) -> Seq Scan on datadb_fix (cost=0.00..121867.99 rows=6729299 width=28) -> Hash (cost=1115.54..1115.54 rows=14438 width=25) -> Hash Join (cost=609.96..1115.54 rows=14438 width=25) Hash Cond: ("outer".legacy_calcdb_id = "inner".serial) -> Seq Scan on security_series (cost=0.00..247.40 rows=15540 width=13) -> Hash (cost=572.37..572.37 rows=15037 width=12) -> Seq Scan on calcdb (cost=0.00..572.37 rows=15037 width=12) pim_new-# Table "obsolete.datadb_fix" pim_new-# Column | Type | Modifiers pim_new-# -------------+------------------+----------- pim_new-# serial | integer | pim_new-# id | integer | pim_new-# date | date | [4 fields deleted] pim_new-# Indexes: sb_data_pkey unique btree (id, date), pim_new-# datadb1_id btree (id), pim_new=# \d obsolete.calcdb Table "obsolete.calcdb" Column | Type | Modifiers --------------------+----------------------+------------------------------------ ------------------- serial | integer | not null default nextval('"calcdb_s erial_seq"'::text) [...30 proprietary fields] Indexes: calcdb_serial_key unique btree (serial), [...5 other indexes] pim_new=# \d security_series Table "public.security_series" Column | Type | Modifiers --------------------+--------------+----------- security_serial_id | integer | not null period | character(1) | not null legacy_calcdb_id | integer | Indexes: security_series_pkey primary key btree (security_serial_id, period), secseries_legacy_id_idx1 btree (legacy_calcdb_id) The target table has three indexes on it, so I suppose that accounts for SOME extra time. I ended up cancelling the query, running the select on a faster machine into an unindexed temp table, then using COPY out and in. That process took about 2.5 hours total. Machine: Linux, PG 7.3.4, 1.1GHz, 768MB RAM, unfortunately running other stuff. The first try which didn't finish in 24 hours was on Mac OS X Jaguar, PG 7.3.3, 1GHz, 256MB (please don't laugh). Yes, hardware upgrades are coming, but I need to estimate how much more I have to squeeze out of the DB and client applications.