Re: Any way to speed this up?

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(at)sss(dot)pgh(dot)pa(dot)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(at)wazagua(dot)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