Tom Lane wrote:
> I bet the problem is that you're not getting a hashed subplan in 8.1.
> What do you have work_mem set to on the two systems? The fact that the
> rowcount estimate for the subplan is much larger may be causing the
> change, too. Why is that --- perhaps you haven't ANALYZEd stud_v
One Addition:
> PostgreSQL 8.1.4 (The one which is taking... a very long time...)
>
> Merge Join (cost=7751.81..50026810.45 rows=7364 width=4)
> Merge Cond: ("outer".sid = "inner".sid)
> -> Index Scan using stud_pkey on stud s (cost=7751.81..49994210.01
> rows=56607 width=4)
> Filt
Joe Conway wrote:
> It is possible for a query to run for many days, and still finish. This
> classifies as slow, not hung. The difference is important in
> troubleshooting to determine the cause.
OK, what do you suggest, how long should the process run, until I can
except it not to end?
>>>Als
Andrew Sullivan wrote:
> No, it doesn't. Deadlock means, for the two deadlocked queries, both
> cannot possibly finish because each waits on a lock that the other
> one holds.
Thanks for the clarification. I thought a deadlock is also, when the
system runs into an endless loop.
>> There is no
> EXPLAIN without ANALYZE locking up?!? Maybe some application is holding
> a lock on a record in your result set.
OK, this was a good hint. I got EXPLAIN working. My mistake was, that I
tried explain the last time, while the 'Killer-SQL' was running. Then
EXPLAIN didn't answer until I killed th
Tom Lane wrote:
> Kai Hessing <[EMAIL PROTECTED]> writes:
>> No one any idea? *sigh*
>
> What makes you think it's a deadlock and not a very slow query? I'd be
> checking if the tables were all ANALYZEd and comparing EXPLAIN output
> to the old database ...
Joe Conway wrote:
> Martijn van Oosterhout wrote:
>> It probably has something to with the fact that you didn't explain what
>> you meant by "deadlock". Also, you refer to a temp table, yet don't
>> indicate which table it is.
Deadlock means it hangs up and doesn't terminate through timeout.
The s
Alban Hertroys wrote:
>>> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid =
>>> 34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 );
>
> I'm pretty sure it's not a deadlock. It probably takes very long for
> some reason; maybe an explain of that query will gi
No one any idea? *sigh*
Kai Hessing wrote:
> Hi Folks,
>
> I have a strange Problem (to be honest there are more than one, but this
> is one of it) after Upgrading to Postgres 8.1.3. The following SQL seems
> to produce a deadlock while doing an endless reading of a temp table:
&g
Hi Folks,
I have a strange Problem (to be honest there are more than one, but this
is one of it) after Upgrading to Postgres 8.1.3. The following SQL seems
to produce a deadlock while doing an endless reading of a temp table:
SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid
Richard Connamacher wrote:
> This came up with an error too, but it pointed me in the right
> direction and was easy to fix. I needed to use an alias for the
> entire subquery too, so what finally worked is this:
>
> SELECT avg(minprice) FROM
> (SELECT min(price) AS minprice FROM weekly_
Kai Hessing wrote:
>>> What do you mean with larger statistics target?
>>
>> See ALTER TABLE SET STATISTICS, or just change default_statistics_target
>> and re-ANALYZE.
>
> Thanks, that definitly looks like a starting point. I will test it and
> post
Tom Lane wrote:
> Well, here's our problem it would seem: the planner is estimating the IN
> clause to match 317227 rows, rather than the actual 2522. That's
> naturally going to bias it against choosing an indexscan. You need to
> get that estimate closer before there's going to be much chance o
Tom Lane wrote:
> Kai Hessing <[EMAIL PROTECTED]> writes:
>> Index Scan using phon_phon_idx on phon (cost=0.00..5193.83 rows=530
>> width=148) (actual time=0.146..0.146 rows=0 loops=1)
>> ...
>> Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actu
Marko Kreen wrote:
> On 3/31/06, Kai Hessing <[EMAIL PROTECTED]> wrote:
>> The second one (UPDATE xyz WHERE id IN (xyz1, xyz2, ) AND
>> status>-1;) returns:
>> --
>> Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual
&
Joshua D. Drake wrote:
> Kai Hessing wrote:
>> 1.) 21.5 seconds
>> 2.) 363.7 seconds
>>
>> But it is still a significant difference.
>
> Can you provide an explain analyze of each query?
There isn't an explain analyze of the first variant, because there
Tormod Omholt-Jensen wrote:
> We are upgrading our systems to handle new languages and therefore we
> need to change the encoding of our postgres database from LATIN1 to UTF-8.
>
> I have pg_dumped the database and imported it into the new UTF-8 base.
> It seems like this worked just fine.
>
>
Hossa,
I just made a little test on our test-database. I have an excel sheet
with about 2000 entries that should be updated with the same value. In a
first try I generated an SQL-Syntax for every entry like:
UPDATE xyz SET status=-6 WHERE id=xyz1 AND status>-1;
UPDATE xyz SET status=-6 WHERE id=x
> and it took about 10 Minutes to execute. So it is nearly a hundred times
> slower. Can this be verified? Is there anything that can be done about
> that? Else I would need to have a few words with our programmers...
OK... I overstated a little bit. The real numbers are:
1.) 21.5 seconds
2.) 363
Bruno Wolff III wrote:
> On Thu, Mar 16, 2006 at 18:11:33 +0100,
> Kai Hessing <[EMAIL PROTECTED]> wrote:
>>
>> The 'status'-row can contain either 1 or -1 and -4. And now I would like
>> to implement the action that if the the status field in the mast
Hi Folks,
I'm not perfekt in SQL so I'm not really sure, how this can be done.
I have a master table stud containing (amongs others) two rows called
'sid' and 'status'. Now I have different tables using constraints to
reference this table. For example I have one table called phon with the
rows 'p
Hi Tom,
thanks for your answer. I did some testing now but still have the same
problem.
Tom Lane wrote:
>> Kai Hessing <[EMAIL PROTECTED]> writes:
>
>>>> [DB-Problem]
>
>>
>> Hmm. This looks like a pg_dump bug, ie, issuing ALTER OWNER commands
>>
Hi Folks,
I have a small problem. We're currently using an old PostgreSQL 7.3.1
database. In near future we want to migrate to a new server and taking
the chance to upgrade postgres. Now I have a testsystem with postgres
8.0.1 where I tried to import a dump from our database. Everthing works
fine
23 matches
Mail list logo
