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.
Description: OpenPGP digital signature