Hi all,
The following query takes about 4s to run in a 16GB ram server. Any ideas
why it doesn´t use index for the primary keys in the join conditions?
select i.inuid, count(*) as total
from cte.instrumentounidade i
inner join cte.pontuacao p on p.inuid = i.inuid
inner join cte.acaoindicador ai
The following query takes about 4s to run in a 16GB ram server. Any ideas
why it doesn´t use index for the primary keys in the join conditions?
Maby random_page_cost is set too high? What version are you using?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest
On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso [EMAIL PROTECTED] wrote:
Hi all,
The following query takes about 4s to run in a 16GB ram server. Any ideas
why it doesn´t use index for the primary keys in the join conditions?
select i.inuid, count(*) as total
from cte.instrumentounidade i
Set random_page_cost = 2 solved the problem. thanks
On Thu, Feb 21, 2008 at 6:16 PM, Claus Guttesen [EMAIL PROTECTED] wrote:
why it doesn´t use index for the primary keys in the join
conditions?
Maby random_page_cost is set too high? What version are you using?
Postgresql v. 8.2.1
Well, all the row counts in expected and actual are pretty close. I'm
guessing it's as optimized as it's likely to get. you could try
mucking about with random_page_cost to force index usage, but indexes
are not always a win in pgsql, hence the seq scans etc... If the
number of rows returned
The other parameter you might want to look at is effective_cache_size -
increasing it will encourage index use. On a machine with 16GB the
default is probably too small (there are various recommendations about
how to set this ISTR either Scott M or Greg Smith had a page somewhere
that covered
On 21-Feb-08, at 6:16 PM, Scott Marlowe wrote:
On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood
[EMAIL PROTECTED] wrote:
The other parameter you might want to look at is
effective_cache_size -
increasing it will encourage index use. On a machine with 16GB the
default is probably too small
Scott Marlowe wrote:
effective_cache_size is pretty easy to set, and it's not real
sensitive to small changes, so guesstimation is fine where it's
concerned. Basically, let your machine run for a while, then add the
cache and buffer your unix kernel has altogether (top and free will
tell you
