Re: Can you help with this JOIN?

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 <wweng(at)kencast(dot)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.