Markus Bertheau wrote:
I'm getting a plan that uses a sequential scan on ext_feeder_item instead of
several index scans, which slows down the query significantly:
# explain analyze select fi.pub_date from ext_feeder_item fi where fi.feed_id
in (select id from ext_feeder_feed ff where ff.is_sy
On 21-Feb-08, at 12:13 AM, bh yuan wrote:
Hi
I am using Postgres8.3 on 8G memory , Xeon X5355 Quad Core x 2
processer RH5 machine with 10G data. (with some table which have
about 2,000,000~ 5,000,000 rows )
I have two quesion.
1. how to set the shared_buffers and other postgresql.conf parame
On Wed, 20 Feb 2008, Tom Lane wrote:
However, this resulted in random errors from Postgres - something to do
with locked tables. So I changed it so that no two threads create indexes
for the same table at once, and that solved it.
How long ago was that? There used to be some issues with two CR
Jeff writes:
> I wonder if it would be worthwhile if pg_restore could emit a warning
> if maint_work_mem is "low" (start flamewar on what "low" is).
>
> And as an addition to that - allow a cmd line arg to have pg_restore
> bump it before doing its work? On several occasions I was moving a
> lar
Guillaume Cottenceau <[EMAIL PROTECTED]> writes:
> I have made a comparison restoring a production dump with default
> and large maintenance_work_mem. The speedup improvement here is
> only of 5% (12'30 => 11'50).
> Apprently, on the restored database, data is 1337 MB[1] and
> indexes 644 MB[2][2]
On Feb 21, 2008, at 12:28 PM, Guillaume Cottenceau wrote:
I have made a comparison restoring a production dump with default
and large maintenance_work_mem. The speedup improvement here is
only of 5% (12'30 => 11'50).
At one point I was evaluating several server vendors and did a bunch
of DB
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 o
HashAggregate (cost=47818.40..47853.12 rows=1984 width=4) (actual time=
5738.879..5743.390 rows=1715 loops=1)
Filter: (count(*) > 0)
-> Hash Join (cost=16255.99..46439.06 rows=183912 width=4) (actual time=
1887.974..5154.207 rows=241693 loops=1)
Hash Cond: (si.aciid = ai.aciid)
-
> 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 gamest
On Thu, Feb 21, 2008 at 6:10 PM, Claus Guttesen <[EMAIL PROTECTED]> wrote:
> > 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
> > > 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
You can try to lower this value. The default (in 8.3) is 4.
--
regards
Claus
When lenity and cruelty play for a kingdom,
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.instrumentouni
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?
> >
> > Pos
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 rep
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 t
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 (there are various recommendations about
> how
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 (
On Thu, Feb 21, 2008 at 5:40 PM, Dave Cramer <[EMAIL PROTECTED]> wrote:
>
> 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 -
> >>
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 t
19 matches
Mail list logo
