Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 588DA4763E7 for ; Thu, 30 May 2002 13:16:53 -0400 (EDT) Received: from mta4.snet.net (mta4.snet.net [204.60.203.76]) by postgresql.org (Postfix) with ESMTP id B2477476254 for ; Thu, 30 May 2002 13:11:25 -0400 (EDT) Received: from pop.snet.net ([204.60.203.73]) by mta4.snet.net (8.12.3/8.12.3/SNET-mx-1.2/D-1.1.1.1/O-1.1.1.1) with ESMTP id g4UHBKHH016850 for ; Thu, 30 May 2002 13:11:20 -0400 (EDT) X-Originating-IP: [64.252.95.167] X-SBCIS-MTA: [pop.snet.net] Received: from [192.168.1.83] (167.95.252.64.snet.net [64.252.95.167]) by pop.snet.net (8.12.3/8.12.3/SNET-pop-1.2/D-1.1.1.1/O-1.1.1.1) with ESMTP id g4UHB0UQ028314 for ; Thu, 30 May 2002 13:11:01 -0400 (EDT) Subject: Re: Can you help with this JOIN? From: Wei Weng To: pgsql-sql@postgresql.org In-Reply-To: <24119.1022690226@sss.pgh.pa.us> References: <1022688374.26733.5.camel@Monet> <24119.1022690226@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.3 Date: 30 May 2002 13:11:22 -0400 Message-Id: <1022778684.32671.3.camel@Monet> Mime-Version: 1.0 X-Archive-Number: 200205/314 X-Sequence-Number: 7949 I was not running 7.2 when I posted this. So I upgraded my system and this is the new query plan EXPLAIN ANALYZE: NOTICE: QUERY PLAN: Limit (cost=30448.71..30450.96 rows=15 width=713) (actual time=7591.70..7592.67 rows=15 loops=1) -> Unique (cost=30448.71..30898.79 rows=3000 width=713) (actual time=7591.69..7592.64 rows=16 loops=1) -> Sort (cost=30448.71..30448.71 rows=30005 width=713) (actual time=7591.69..7591.78 rows=46 loops=1) -> Hash Join (cost=2310.20..5107.41 rows=30005 width=713) (actual time=1064.93..5288.91 rows=30005 loops=1) -> Hash Join (cost=2310.19..4955.50 rows=30005 width=529) (actual time=1064.87..5053.43 rows=30005 loops=1) -> Hash Join (cost=2309.17..4429.39 rows=30005 width=489) (actual time=1064.63..3944.85 rows=30005 loops=1) -> Merge Join (cost=2307.99..3903.13 rows=30005 width=449) (actual time=1064.36..2842.60 rows=30005 loops=1) -> Index Scan using contentattributes_contentid_ind on contentattributes cab (cost=0.00..1120.06 rows=30005 width=80) (actual time=20.71..459.11 rows=30005 loops=1) -> Sort (cost=2307.99..2307.99 rows=10002 width=369) (actual time=1043.57..1162.89 rows=30003 loops=1) -> Hash Join (cost=1.02..562.08 rows=10002 width=369) (actual time=0.33..462.06 rows=10002 loops=1) -> Seq Scan on contentsummaries cs (cost=0.00..386.02 rows=10002 width=320) (actual time=0.09..126.02 rows=10002 loops=1) -> Hash (cost=1.02..1.02 rows=2 width=49) (actual time=0.11..0.11 rows=0 loops=1) -> Seq Scan on contenttypes ct (cost=0.00..1.02 rows=2 width=49) (actual time=0.10..0.10 rows=2 loops=1) -> Hash (cost=1.14..1.14 rows=14 width=40) (actual time=0.17..0.17 rows=0 loops=1) -> Seq Scan on attributes ab (cost=0.00..1.14 rows=14 width=40) (actual time=0.09..0.14 rows=14 loops=1) -> Hash (cost=1.02..1.02 rows=2 width=40) (actual time=0.13..0.13 rows=0 loops=1) -> Seq Scan on categories cat (cost=0.00..1.02 rows=2 width=40) (actual time=0.11..0.12 rows=2 loops=1) -> Hash (cost=0.00..0.00 rows=1 width=184) (actual time=0.01..0.01 rows=0 loops=1) -> Seq Scan on publishers pb (cost=0.00..0.00 rows=1 width=184) (actual time=0.00..0.00 rows=0 loops=1) Total runtime: 7687.20 msec Hope this makes sense. Thanks On Wed, 2002-05-29 at 12:37, Tom Lane wrote: > Wei Weng writes: > > I have a massive join that takes something like 10 seconds to execute in > > Postgresql 7.2. Can any of gurus here help me improve it? > > 1. Have you vacuum analyzed these tables lately? The row count > estimates look suspiciously low. > > 2. Please provide EXPLAIN ANALYZE output not just EXPLAIN. > > regards, tom lane > -- Wei Weng Network Software Engineer KenCast Inc.