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 3E896D1F693 for ; Fri, 20 Feb 2004 21:58:03 +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 50285-08 for ; Fri, 20 Feb 2004 17:58:02 -0400 (AST) Received: from stork.mail.pas.earthlink.net (stork.mail.pas.earthlink.net [207.217.120.188]) by svr1.postgresql.org (Postfix) with ESMTP id 2E02BD1F696 for ; Fri, 20 Feb 2004 17:58:00 -0400 (AST) Received: from dpc6682165056.direcpc.com ([66.82.165.56] helo=earthlink.net) by stork.mail.pas.earthlink.net with asmtp (Exim 3.33 #1) id 1AuIei-0002C1-00 for pgsql-performance@postgresql.org; Fri, 20 Feb 2004 13:57:59 -0800 Message-ID: <403682D6.60807@earthlink.net> Date: Fri, 20 Feb 2004 16:57:42 -0500 From: Sean Shanny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: General performance questions about postgres on Apple References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-ELNK-Trace: 6ca696a38bf90a1a1de288fa2098067a1aa676d7e74259b7b3291a7d08dfec792fe6a6948540acc70a2ecd975965c1a9350badd9bab72f9c350badd9bab72f9c X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/243 X-Sequence-Number: 5767 scott.marlowe wrote: >On Fri, 20 Feb 2004, Sean Shanny wrote: > > > >>max_connections = 100 >> >># - Memory - >> >>shared_buffers = 16000 # min 16, at least max_connections*2, >>8KB each >>sort_mem = 256000 # min 64, size in KB >> >> > >You might wanna drop sort_mem somewhat and just set it during your imports >to something big like 512000 or larger. That way with 100 users during >the day you won't have to worry about swap storms, and when you run your >updates, you get all that sort_mem. > > > >>Actual row count in the temp table: >> >>select count(*) from referral_temp ; >> 502347 >> >>Actual row count in d_referral table: >> >>select count(*) from d_referral ; >> 27908024 >> >> >>Note: that an analyze had not been performed on the referral_temp table >>prior to the explain analyze run. >> >>explain analyze SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT >>OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5 >> >>Nested Loop Left Join (cost=0.00..3046.00 rows=1001 width=68) (actual >>time=136.513..6440616.541 rows=502347 loops=1) >> -> Seq Scan on referral_temp t2 (cost=0.00..20.00 rows=1000 >>width=64) (actual time=21.730..10552.421 rows=502347 loops=1) >> -> Index Scan using d_referral_referral_md5_key on d_referral t1 >>(cost=0.00..3.01 rows=1 width=40) (actual time=12.768..14.022 rows=1 >>loops=502347) >> Index Cond: ("outer".md5 = t1.referral_md5) >> >> >>Thanks. >> >>--sean >> Total runtime: 6441969.698 ms >>(5 rows) >> >> >>Here is an explain analyze after the analyze was done. Unfortunately I >>think a lot of the data was still in cache when I did this again :-( >> >>explain analyze SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT >>OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5; >> >>Nested Loop Left Join (cost=0.00..1468759.69 rows=480082 width=149) >>(actual time=69.576..3226854.850 rows=502347 loops=1) >> -> Seq Scan on referral_temp t2 (cost=0.00..16034.81 rows=480081 >>width=145) (actual time=11.206..4003.521 rows=502347 loops=1) >> -> Index Scan using d_referral_referral_md5_key on d_referral t1 >>(cost=0.00..3.01 rows=1 width=40) (actual time=6.396..6.402 rows=1 >>loops=502347) >> Index Cond: ("outer".md5 = t1.referral_md5) >> Total runtime: 3227830.752 ms >> >> > >Hmmm. It looks like postgresql is still picking a nested loop when it >should be sorting something faster. Try doing a "set enable_nestloop = >off" and see what you get. > > New results with the above changes: (Rather a huge improvement!!!) Thanks Scott. I will next attempt to make the cpu_* changes to see if it the picks the correct plan. explain analyze SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=1669281.60..3204008.48 rows=480082 width=149) (actual time=157221.125..-412311.378 rows=502347 loops=1) Hash Cond: ("outer".md5 = "inner".referral_md5) -> Seq Scan on referral_temp t2 (cost=0.00..16034.81 rows=480081 width=145) (actual time=11.537..1852.336 rows=502347 loops=1) -> Hash (cost=1356358.48..1356358.48 rows=30344048 width=40) (actual time=157187.530..157187.530 rows=0 loops=1) -> Seq Scan on d_referral t1 (cost=0.00..1356358.48 rows=30344048 width=40) (actual time=14.134..115048.285 rows=27908024 loops=1) Total runtime: 212595.909 ms (6 rows) Time: 213094.984 ms tripmaster=# explain analyze SELECT t1.id, t2.md5, t2.url from url_temp t2 LEFT OUTER JOIN d_url t1 ON t2.md5 = t1.url_md5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Hash Left Join (cost=2023843.40..3157938.15 rows=1379872 width=191) (actual time=178150.113..867074.579 rows=1172920 loops=1) Hash Cond: ("outer".md5 = "inner".url_md5) -> Seq Scan on url_temp t2 (cost=0.00..50461.72 rows=1379872 width=187) (actual time=6.597..6692.324 rows=1172920 loops=1) -> Hash (cost=1734904.72..1734904.72 rows=28018272 width=40) (actual time=178124.568..178124.568 rows=0 loops=1) -> Seq Scan on d_url t1 (cost=0.00..1734904.72 rows=28018272 width=40) (actual time=16.912..2639059.078 rows=23239137 loops=1) Total runtime: 242846.965 ms (6 rows) Time: 243190.900 ms >If that makes it faster, you may want to adjust the costs of the cpu_* >stuff higher to see if that can force it to do the right thing. > >Looking at the amount of time taken by the nested loop, it looks like the >problem to me. > >And why are you doing a left join of ONE row from one table against the >whole temp table? Do you really need to do that? since there's only one >row in the source table, and I'd guess is only matches one or a few rows >from the temp table, this means you're gonna have that one row and a bunch >of null filled rows to go with it. > > > >