From: John Arbash Meinel <john@arbash-meinel.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: PostgreSQL Perform <pgsql-performance@postgresql.org>
Subject: Re: Any way to speed this up?
Date: Thu, 07 Apr 2005 10:36:54 -0500
Message-ID: <42555396.40303@arbash-meinel.com> (raw)
In-Reply-To: <005301c53b84$6b83f8a0$797ba8c0@jfradkin>
References: <005301c53b84$6b83f8a0$797ba8c0@jfradkin>
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, jfradkin@wazagua.com
Subject: Re: Any way to speed this up?
In-Reply-To: <42555396.40303@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox