Re: Any way to speed this up? - Mailing list pgsql-performance

2 things to point out from this last run:

50% of the time is taken scanning tblassociate
    ->  Seq Scan on tblassociate a  (cost=0.00..38388.79 rows=199922 width=53) (actual time=62.000..10589.000
rows=176431loops=1) 
          Filter: ((clientnum)::text = 'SAKS'::text)

If you had an index on clientnum and didn't cast it to text in the view,
you might be able to use an indexscan, which could be faster (depends on
how big the table actually is).

This sort is taking about 25% of the time:
  ->  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=176431loops=1) 

I suspect it shouldn't take 5 seconds to sort 160k rows in memory, and
that this sort is spilling to disk. If you increase your working memory
the sort might fit entirely in memory. As a quick test, you could set
working memory to 80% of system memory and see how that changes the
speed. But you wouldn't want to set it that high in production.

On Thu, Apr 07, 2005 at 01:14:33PM -0400, Joel Fradkin wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"