[EMAIL PROTECTED] (Josh Berkus) wrote:
> I've been trying to peg the "sweet spot" for shared memory using
> OSDL's equipment. With Jan's new ARC patch, I was expecting that
> the desired amount of shared_buffers to be greatly increased. This
> has not turned out to be the case.
That doesn't surp
Tom,
> BTW, what is the actual size of the test database (disk footprint wise)
> and how much of that do you think is heavily accessed during the run?
> It's possible that the test conditions are such that adjusting
> shared_buffers isn't going to mean anything anyway.
The raw data is 32GB, but a
Tom,
> This does raise a question for Josh though, which is "where's the
> oprofile results?" ÂIf we do have major problems at the level of cache
> misses then oprofile would be able to prove it.
Missing, I'm afraid. OSDL has been having technical issues with STP all week.
Hopefully the next te
On Fri, Oct 08, 2004 at 06:32:32PM -0400, Tom Lane wrote:
> This does raise a question for Josh though, which is "where's the
> oprofile results?" If we do have major problems at the level of cache
> misses then oprofile would be able to prove it.
Or cachegrind. I've found it to be really effecti
"J. Andrew Rogers" <[EMAIL PROTECTED]> writes:
> As I understand it (and I haven't looked so I could be wrong), the
> buffer cache is searched by traversing it sequentially.
You really should look first.
The main-line code paths use hashed lookups. There are some cases that
do linear searches th
Josh Berkus <[EMAIL PROTECTED]> writes:
> Here's a top-level summary:
> shared_buffers% RAM NOTPM20*
> 1000 0.2%1287
> 23000 5% 1507
> 46000 10% 1481
> 69000 15% 1382
I have an idea that makes some assumptions about internals that I think
are correct.
When you have a huge number of buffers in a list that has to be
traversed to look for things in cache, e.g. 100k, you will generate an
almost equivalent number of cache line misses on the processor to jump
through
Folks,
I'm hoping that some of you can shed some light on this.
I've been trying to peg the "sweet spot" for shared memory using OSDL's
equipment. With Jan's new ARC patch, I was expecting that the desired
amount of shared_buffers to be greatly increased. This has not turned out to
be the c
On 8 Oct 2004 at 16:04, Tom Lane wrote:
> "Gary Doades" <[EMAIL PROTECTED]> writes:
> > If I remove the redundant clauses, the planner now estimates 1000 rows returned
> > from
> > the table, not unreasonable since it has no statistics. But *why* in that case,
> > with *more*
> > estimated row
"Gary Doades" <[EMAIL PROTECTED]> writes:
> If I remove the redundant clauses, the planner now estimates 1000 rows returned from
> the table, not unreasonable since it has no statistics. But *why* in that case, with
> *more*
> estimated rows does it choose to materialize that table (26 rows) 573
Oops, forgot to mention:
PostgreSQL 8.0 beta 2 Windows.
Thanks,
Gary.
On 8 Oct 2004 at 20:32, Gary Doades wrote:
>
> I'm looking at one of my standard queries and have encountered some strange
> performance
> problems.
>
> The query below is to search for vacant staff member date/time slots
I'm looking at one of my standard queries and have encountered some strange
performance problems.
The query below is to search for vacant staff member date/time slots given a series of
target date/times. The data contained in the booking_plan/staff_booking tables contain
the existing bookin
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same table onto
Pallav Kalva <[EMAIL PROTECTED]> writes:
> I have a problem with the below query, when i do explain on the
> below query on my live database it doesnt use any index specified on the
> tables and does seq scan on the table which is 400k records. But if i
> copy the same table onto a differe
Hi,
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same table onto a different database on a different machine it
uses al
<[EMAIL PROTECTED]> writes:
> Unfortunately, yes thats true - thats is for correctness, not an
> optimization decision. Outer joins constrain you on both join order AND
> on join type. Nested loops and hash joins avoid touching all rows in
> the right hand table, which is exactly what you don't wan
Dawid Kuroczko <[EMAIL PROTECTED]> writes:
> But when I phrase the query:
> SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33) OR
> (icount(ids) > 1 AND ids && '{33}');
> Planner insists on using seqscan. Even with enable_seqscan = off;
The OR-index-scan mechanism isn't currently smar
=?ISO-8859-1?Q?=22Alban_M=E9dici_=28NetCentrex=29=22?= <[EMAIL PROTECTED]> writes:
> Thanks for your repply, but I still don"t understand why the statistic
> logs :
> ! 0/0 [0/0] filesystem blocks in/out
> it told me there is no hard disk access, I'm sure there is,
Complain to your friend
Josh Berkus <[EMAIL PROTECTED]> wrote on 08.10.2004, 07:53:26:
>
> > It's not so much that they are necessarily inefficient as that they
> > constrain the planner's freedom of action. You need to think a lot more
> > carefully about the order of joining than when you use inner joins.
>
> I've a
In article <[EMAIL PROTECTED]>,
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <[EMAIL PROTECTED]> writes:
> disclaimer : brainless proposition
> (SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33)
> UNION ALL
> (SELECT * FROM table WHERE (icount(ids) > 1 AND ids && '{33}'));
I g
Hashing is at least as fast, if not faster.
regards, tom lane
Probably quite faster if the dataset is not huge...
UniqueSort would be useful for GROUP BY x ORDER BY x though
---(end of broadcast)---
TIP 3: if posti
disclaimer : brainless proposition
(SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33)
UNION ALL
(SELECT * FROM table WHERE (icount(ids) > 1 AND ids && '{33}'));
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
I have a large table with a column:
ids integer[] not null
most of these entries (over 95%) contain only one array element, some
can contain up to 10 array elements. seqscan is naturally slow. GIST
on int_array works nice, but GIST isn't exactly a speed daemon when it
comes to updating.
So I th
The really tricky part is that a DISTINCT ON needs to know about a
first()
aggregate. And to make optimal use of indexes, a last() aggregate as
well. And
ideally the planner/executor needs to know something is magic about
first()/last() (and potentially min()/max() at some point) and that they
Thanks for your repply, but I still don"t understand why the
statistic logs :
! 0/0 [0/0] filesystem blocks in/out
it told me there is no hard disk access, I'm sure there is, I
heard my HDD, and see activity using gkrellm (even using my first
query ; big select *) ?
2004-10-08 10
25 matches
Mail list logo
