Tom Lane wrote:
> Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
> The next problem seems to be the drastic misestimation of this join
> size:
>     ->  Nested Loop  (cost=0.00..6872092.36 rows=135 width=28) (actual 
> time=94.762..14429291.129 rows=3554044 loops=1)
>           ->  Merge Join  (cost=0.00..519542.74 rows=449804 width=16) (actual 
> time=48.197..49636.006 rows=474008 loops=1)
>                 Merge Cond: (part.p_partkey = partsupp.ps_partkey)
>                 ->  Index Scan using pk_part on part  (cost=0.00..105830.22 
> rows=112447 width=4) (actual time=34.646..14381.644 rows=118502 loops=1)
>                       Filter: ((p_name)::text ~~ '%ghost%'::text)
>                 ->  Index Scan using i_ps_partkey on partsupp  
> (cost=0.00..388943.05 rows=8000278 width=12) (actual time=13.511..22659.364 
> rows=7999685 loops=1)
>           ->  Index Scan using i_l_suppkey_partkey on lineitem  
> (cost=0.00..14.11 rows=1 width=24) (actual time=4.415..30.310 rows=7 
> loops=474008)
>                 Index Cond: ((partsupp.ps_partkey = lineitem.l_partkey) AND 
> (partsupp.ps_suppkey = lineitem.l_suppkey))
> With a factor-of-25000 error in that rowcount estimate, it's amazing the
> plans aren't worse than they are.
> It evidently thinks that most of the rows in the join of part and
> partsupp won't have any matching rows in lineitem, whereas on average
> there are about 7 matching rows apiece.  So that's totally wacko, and
> it's not immediately obvious why.  Could we see the pg_stats entries for
> part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey,
> lineitem.l_partkey, lineitem.l_suppkey?


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to