pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joel Fradkin <jfradkin@wazagua.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'PostgreSQL Perform' <pgsql-performance@postgresql.org>
Subject: Re: Any way to speed this up?
Date: Thu, 07 Apr 2005 13:14:33 -0400
Message-ID: <007b01c53b95$450c5850$797ba8c0@jfradkin> (raw)
In-Reply-To: <11372.1112892168@sss.pgh.pa.us>

Here is the result after putting it back to 4 the original value (I had done
that prior to your suggestion of using 2 or 3) to see what might change.
I also vacummed and thought I saw records deleted in associate, which I
found odd as this is a test site and no new records were added or deleted.

"Merge Join  (cost=86788.09..87945.00 rows=10387 width=112) (actual
time=19703.000..21154.000 rows=159959 loops=1)"
"  Merge Cond: ("outer".locationid = "inner".locationid)"
"  ->  Sort  (cost=1245.50..1246.33 rows=332 width=48) (actual
time=62.000..62.000 rows=441 loops=1)"
"        Sort Key: l.locationid"
"        ->  Index Scan using ix_location on tbllocation l
(cost=0.00..1231.60 rows=332 width=48) (actual time=15.000..62.000 rows=441
loops=1)"
"              Index Cond: ('SAKS'::text = (clientnum)::text)"
"  ->  Sort  (cost=85542.59..86042.39 rows=199922 width=75) (actual
time=19641.000..19955.000 rows=159960 loops=1)"
"        Sort Key: a.locationid"
"        ->  Merge Right Join  (cost=60850.40..62453.22 rows=199922
width=75) (actual time=13500.000..14734.000 rows=176431 loops=1)"
"              Merge Cond: (("outer".id = "inner".jobtitleid) AND
("outer"."?column4?" = "inner"."?column10?"))"
"              ->  Sort  (cost=554.11..570.13 rows=6409 width=37) (actual
time=94.000..94.000 rows=6391 loops=1)"
"                    Sort Key: jt.id, (jt.clientnum)::text"
"                    ->  Seq Scan on tbljobtitle jt  (cost=0.00..148.88
rows=6409 width=37) (actual time=0.000..63.000 rows=6391 loops=1)"
"                          Filter: (1 = presentationid)"
"              ->  Sort  (cost=60296.29..60796.09 rows=199922 width=53)
(actual time=13406.000..13859.000 rows=176431 loops=1)"
"                    Sort Key: a.jobtitleid, (a.clientnum)::text"
"                    ->  Seq Scan on tblassociate a  (cost=0.00..38388.79
rows=199922 width=53) (actual time=62.000..10589.000 rows=176431 loops=1)"
"                          Filter: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 22843.000 ms"

Joel Fradkin
 
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Thursday, April 07, 2005 11:43 AM
To: Joel Fradkin
Cc: 'PostgreSQL Perform'
Subject: Re: [PERFORM] Any way to speed this up? 

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> random_page_cost = 1.2#4		# units are one sequential page
> fetch cost

That is almost certainly overoptimistic; it's causing the planner to
use indexscans when it shouldn't.  Try 2 or 3 or thereabouts.

			regards, tom lane




view thread (11+ messages)  latest in thread

Message-ID: <007b01c53b95$450c5850$797ba8c0@jfradkin>
Permalink:  ../007b01c53b95$450c5850$797ba8c0@jfradkin/
Also on:    postgresql.org/message-id/007b01c53b95$450c5850$797ba8c0@jfradkin

 · 

reply

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: jfradkin@wazagua.com, tgl@sss.pgh.pa.us
  Subject: Re: Any way to speed this up?
  In-Reply-To: <007b01c53b95$450c5850$797ba8c0@jfradkin>

* 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