Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> http://www.kaltenbrunner.cc/files/analyze_q9_beta1.txt
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.22rows=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.05rows=8000278 width=12) (actual time=13.511..22659.364 rows=7999685 loops=1) -> Index Scan
usingi_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?
regards, tom lane