Litao Wu <[EMAIL PROTECTED]> writes:
> When I included a subquery, the estimated rows (1240)
> is way too high as shown in the following example.
> select * from test
> where scope=(select 10);
The planner sees that as "where scope = "
and falls back to a default estimate. It won't simplify a s
> stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM
> Trank_work_overall GROUP BY work_units;
>
> ...
>
> raw_rank | bigint |
> work_units | bigint |
If you create a copy of the same table using regular integers does that run
fast? And a copy of the table using bigin
On Fri, 16 Apr 2004 10:34:49 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
>> p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}}
>
>So? You haven't proven that either sampling method fails to do the
>same.
On the contrary, I believe that above formula is more or less valid for
both meth
Hi,
When I included a subquery, the estimated rows (1240)
is way too high as shown in the following example.
Can someone explain why? Because of this behavior,
some of our queries use hash join instead of nested
loop.
Thanks,
select version();
version
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
A better way to set this would be to run VACUUM VERBOSE ANALYZE right after
doing one of your update batches, and see how many dead pages are being
reclaimed, and then set max_fsm_pages to that # + 50% (or more).
Actually, since he'
On Friday 16 April 2004 5:12 pm, Tom Lane wrote:
> Chris Kratz <[EMAIL PROTECTED]> writes:
> > ... Or if worse comes to worse to actually kill long running
> > processes without taking down the whole db as we have had to do on
> > occasion.
>
> A quick "kill -INT" suffices to issue a query cancel,
Chris Kratz <[EMAIL PROTECTED]> writes:
> ... Or if worse comes to worse to actually kill long running
> processes without taking down the whole db as we have had to do on occasion.
A quick "kill -INT" suffices to issue a query cancel, which I think is
what you want here. You could also consider
On Friday 16 April 2004 4:25 pm, Mike Nolan wrote:
> Given the intermittent nature of the problem and its relative brevity
> (5-10 seconds), I don't know whether top offers the granularity needed to
> locate the bottleneck.
Our long running processes run on the order of multiple minutes (sometimes
> Fairly sure, when it is happening, postgres usually is taking up the top slots
> for cpu usage as reported by top. Perhaps there is a better way to monitor
> this?
Given the intermittent nature of the problem and its relative brevity
(5-10 seconds), I don't know whether top offers the granul
Josh Berkus <[EMAIL PROTECTED]> writes:
> A better way to set this would be to run VACUUM VERBOSE ANALYZE right after
> doing one of your update batches, and see how many dead pages are being
> reclaimed, and then set max_fsm_pages to that # + 50% (or more).
Actually, since he's running 7.4, the
Ron,
> Yeah I agree but I'm not allowed to remove those indexes.
It's not the indexes I'm talking about, it's the table.
> On my dev server I increased max_fsm_pages from the default of 2 to
> 4,
A better way to set this would be to run VACUUM VERBOSE ANALYZE right after
doing one of
Thanks Mallah,
I will keep this example in case I need it again sometime in the future.
Unfortunately, I do not have enough free space at the moment to create a
temp table.
Dan
-Original Message-
From: Rajesh Kumar Mallah [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 10:27 AM
T
Fairly sure, when it is happening, postgres usually is taking up the top slots
for cpu usage as reported by top. Perhaps there is a better way to monitor
this?
The other thing for us is that others talk about disks being the bottleneck
whereas for us it is almost always the processor. I expec
Josh Berkus wrote:
Ron,
The emp table has 60 columns, all indexed, about two-thirds are numeric,
but they are not affected by this update. The other 50+ columns are
updated in the middle of the night and the amount of time that update
takes isn't a concern.
Well, I'd say that you have
Dirk,
> I'm not sure if this semop() problem is still an issue but the database
> behaves a bit out of bounds in this situation, i.e. consuming system
> resources with semop() calls 95% while tables are locked very often and
> longer.
It would be helpful to us if you could test this with the i
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Combined with the hash aggregate problem I saw (see my other email to
> the list), do you think there could be some issue with the performance
> of the hash function on FreeBSD 5.2 on AMD64?
Yeah, I was wondering about that too. Hard to imagine what th
Yes, stats are up to date, and the values should be fairly unique.
Combined with the hash aggregate problem I saw (see my other email to
the list), do you think there could be some issue with the performance
of the hash function on FreeBSD 5.2 on AMD64?
I'll post the table you requested someplace
Ron,
> The emp table has 60 columns, all indexed, about two-thirds are numeric,
> but they are not affected by this update. The other 50+ columns are
> updated in the middle of the night and the amount of time that update
> takes isn't a concern.
Well, I'd say that you have an application desi
I am using postgres 7.4.1 and have a problem with a plpgsql function.
When I run the function on the production server it takes approx 33
minutes to run. I dumped the DB and copied it to a similarly configured
box and ran the function and it ran in about 10 minutes. Can anyone
offer advice on t
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Anyone have any ideas why this query would be so slow?
That seems very bizarre. Would you be willing to send me a dump of the
table off-list?
regards, tom lane
---(end of broadcast)-
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Note the time for the hash join step:
Have you ANALYZEd these tables lately?
It looks to me like it's hashing on some column that has only a small
number of distinct values, so that the hash doesn't actually help to
avoid comparisons. The planner shou
> We have a web app with a postgres backend. Most queries have subsecond
> response times through the web even with high usage. Every once in awhile
> someone will run either an ad-hoc query or some other long running db
> process.
Are you sure it is postgres where the delay is occurring?
Note the time for the hash join step:
--
Hash Join (cost=357.62..26677.99 rows=93668 width=62) (actual
time=741.159..443381.011 rows=49091 loops=1)
Ha
On Tue, 2004-04-13 at 15:18, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> Well, the first problem is why is ANALYZE's estimate of the total row
> count so bad :-( ? I suspect you are running into the situation where
> the initial pages of the table are thinly populated and ANALYZE
Anyone have any ideas why this query would be so slow?
stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM
Trank_work_overall GROUP BY work_units;
QUERY PLAN
--
Hello all,
My apologies if this is not the right mailing list to ask this question, but
we are wondering about general performance tuning principles for our main db
server.
We have a web app with a postgres backend. Most queries have subsecond
response times through the web even with high usa
Manfred Koizar <[EMAIL PROTECTED]> writes:
> If the number of pages is B and the sample size is n, a perfect sampling
> method collects a sample where all tuples come from different pages with
> probability (in OpenOffice.org syntax):
> p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i
Just a note, I was trying the cluster command and was short on space. I
figured I had enough space for the new table and index. It failed on me
twice.
The reason is that I noticed for the command to complete, it needed the
space of the new table and 2x the space of the new index.
It looks like
=?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= <[EMAIL PROTECTED]> writes:
> This was the key to look at: we were missing all indices on table which
> is used heavily and does lots of locking. After recreating the missing
> indices the production system performed normal. No, more excessive
> semop() calls, l
Tom, Josh,
I think we have the problem resolved after I found the following note
from Tom:
> A large number of semops may mean that you have excessive contention
on some lockable
> resource, but I don't have enough info to guess what resource.
This was the key to look at: we were missing all i
On Thu, 15 Apr 2004 20:18:49 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
>> getting several tuples from the same page is more likely
>> than with the old method.
>
>Hm, are you sure?
Almost sure. Let's look at a corner case: What is the probability of
getting a sample with no two tuples from the
I am running an update on the same table
update rfis set inquiry_status='APPROVED' where inquiry_status='a';
Its running for past 20 mins. and top output is below.
The PID which is executing the query above is 6712. Can anyone
tell me why it is in an uninterruptable sleep and does it relat
>Josh Berkus
> > Treating the optimizer as a black box is something I'm very
> used to from
> > other RDBMS. My question is, how can you explicitly
> re-write a query now
> > to "improve" it? If there's no way of manipulating queries without
> > actually re-writing the optimizer, we're now in a po
33 matches
Mail list logo
