Joel Fradkin wrote:
> Running this explain on windows box, but production on linux both 8.0.1
>
> The MSSQL is beating me out for some reason on this query.
>
> The linux box is much more powerful, I may have to increase the cache,
> but I am pretty sure its not an issue yet.
>
> It has 8 gig internal memory any recommendation on the cache size to use?
>
>
>
> explain analyze select * from viwassoclist where clientnum = 'SAKS'
>
>
>
> "Merge Join (cost=59871.79..60855.42 rows=7934 width=112) (actual
> time=46906.000..48217.000 rows=159959 loops=1)"
>
The first thing I noticed was this. Notice that the estimated rows is
8k, the actual rows is 160k. Which means the planner is mis-estimating
the selectivity of your merge.
> " -> Sort (cost=59478.03..59909.58 rows=172618 width=75) (actual
> time=46844.000..46985.000 rows=159960 loops=1)"
>
> " Sort Key: a.locationid"
>
This sort actually isn't taking very long. It starts at 46800 and runs
until 47000 so it takes < 1 second.
> " -> Merge Right Join (cost=0.00..39739.84 rows=172618
> width=75) (actual time=250.000..43657.000 rows=176431 loops=1)"
>
> " Merge Cond: ((("outer".clientnum)::text =
> ("inner".clientnum)::text) AND ("outer".id = "inner".jobtitleid))"
>
> " -> Index Scan using ix_tbljobtitle_id on tbljobtitle
> jt (cost=0.00..194.63 rows=6391 width=37) (actual
> time=32.000..313.000 rows=5689 loops=1)"
>
> " Filter: (1 = presentationid)"
>
> " -> Index Scan using ix_tblassoc_jobtitleid on
> tblassociate a (cost=0.00..38218.08 rows=172618 width=53) (actual
> time=31.000..41876.000 rows=176431 loops=1)"
>
> " Index Cond: ((clientnum)::text = 'SAKS'::text)"
>
This is where the actual expense is. The merge right join starts at 250,
and runs until 43000. Which seems to be caused primarily by the index
scan of tblassociate. How many rows are in tblassociate? I'm assuming
quite a bit, since the planner thinks an index scan is faster than seq
scan for 170k rows. (If you have > 2M this is probably accurate)
I don't really know how long this should take, but 38s for 172k rows
seems a little long.
John
=:->