Re: Can you help with this JOIN? - Mailing list pgsql-sql

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
Scanusing
 
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)                                           ->
HashJoin 
 
(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 <wweng@kencast.com> 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.