From: "Mauro N. Infantino" <mauroi@digbang.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Very specific server situation 
Date: Sat, 28 Apr 2007 17:43:01 -0300
Message-ID: <000b01c789d5$d0827b40$ec01010a@intranet.db>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_000C_01C789BC.AB354340"
In-Reply-To: <6665.1177714296@sss.pgh.pa.us>
Thread-Index: AceJHlMgbpCSE6WfTqmL50HXsMF6FAAGuJBA

This is a multi-part message in MIME format.

------=_NextPart_000_000C_01C789BC.AB354340
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit

Tom, 

Thank you very much for your suggestions.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> 
> Have you checked to make sure the query plans are reasonable? 

I've attached the main query and its explain plan. I can't find a way to
improve it.

Does it make any difference if it's executed from a stored procedure? Is
there any difference between the SP's language (PL/pgSQL, PL/php, etc. It
needs to make some other tiny things besides the query)?

> You might want to try contrib/pgstattuple

Thanks. I'll give it a try and report the results here.

> absolutely critical to boost checkpoint_segments far enough 

How do I know how ofen checkpoints are done?
I've modified the parameters:

checkpoint_segments = 36 # it was 12 before
checkpoint_timeout = 1000
checkpoint_warning = 300 # so, I'll get a warning if it's too frequent.
commit_delay = 5000
commit_siblings = 2

> adjusting bgwriter and/or vacuum cost delay parameters

I've used a moderate cost delay configuration to see how it responds
(vacuum_cost_delay = 100 & vacuum_cost_limit = 200).
Do you have any advice on how to configure the bgwriter? I have no clue
about it and couldn't find anything clear.

Also, I know an upgrade to 8.2 is always a good thing, but is there any
change that could help this specific situation?

Again, thank you very much for your answers (and, of course, everything you
do in pgsql).

Regards,
Mauro.

------=_NextPart_000_000C_01C789BC.AB354340
Content-Type: text/plain;
	name="plan.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="plan.txt"

SELECT		Player.Title,
		Player.IdPlayer
FROM		Player
		INNER JOIN ActivePlayer ON ActivePlayer.IdPlayer =3D Player.IdPlayer
		INNER JOIN PlayerCategoryShow ON PlayerCategoryShow.IdCategory =3D 18 =
AND PlayerCategoryShow.IdPlayer =3D Player.IdPlayer
WHERE		IdLanguage =3D 'EN'
		AND Player.IdPlayer NOT IN (279)
		AND Player.CreditsAvailable
ORDER BY	RANDOM()
LIMIT		8;

Limit  (cost=3D36760.49..36760.51 rows=3D8 width=3D25) (actual =
time=3D130.359..130.375 rows=3D8 loops=3D1)
  ->  Sort  (cost=3D36760.49..36763.90 rows=3D1364 width=3D25) (actual =
time=3D130.355..130.360 rows=3D8 loops=3D1)
        Sort Key: random()
        ->  Hash Join  (cost=3D36252.38..36689.47 rows=3D1364 =
width=3D25) (actual time=3D87.092..126.733 rows=3D963 loops=3D1)
              Hash Cond: ("outer".idplayer =3D "inner".idplayer)
              ->  Seq Scan on activeplayer  (cost=3D0.00..276.45 =
rows=3D19145 width=3D4) (actual time=3D0.014..20.865 rows=3D19163 =
loops=3D1)
              ->  Hash  (cost=3D36248.24..36248.24 rows=3D1654 =
width=3D29) (actual time=3D86.901..86.901 rows=3D1250 loops=3D1)
                    ->  Hash Join  (cost=3D35685.70..36248.24 =
rows=3D1654 width=3D29) (actual time=3D58.835..84.643 rows=3D1250 =
loops=3D1)
                          Hash Cond: ("outer".idplayer =3D =
"inner".idplayer)
                          ->  Bitmap Heap Scan on playercategoryshow  =
(cost=3D54.96..493.96 rows=3D8560 width=3D4) (actual =
time=3D2.445..14.130 rows=3D8397 loops=3D1)
                                Recheck Cond: (idcategory =3D 18)
                                ->  Bitmap Index Scan on =
ix_playercategoryshow  (cost=3D0.00..54.96 rows=3D8560 width=3D0) =
(actual time=3D2.328..2.328 rows=3D8401 loops=3D1)
                                      Index Cond: (idcategory =3D 18)
                          ->  Hash  (cost=3D35619.53..35619.53 =
rows=3D4484 width=3D25) (actual time=3D56.294..56.294 rows=3D4114 =
loops=3D1)
                                ->  Bitmap Heap Scan on player  =
(cost=3D5296.33..35619.53 rows=3D4484 width=3D25) (actual =
time=3D12.943..49.075 rows=3D4114 loops=3D1)
                                      Recheck Cond: ((idlanguage)::text =
=3D 'EN'::text)
                                      Filter: ((idplayer <> 279) AND =
creditsavailable)
                                      ->  Bitmap Index Scan on =
ix_player3  (cost=3D0.00..5296.33 rows=3D15809 width=3D0) (actual =
time=3D9.868..9.868 rows=3D16245 loops=3D1)
                                            Index Cond: =
((idlanguage)::text =3D 'EN'::text
Total runtime: 130.946 ms
------=_NextPart_000_000C_01C789BC.AB354340--