From: | Paul Raines <raines(at)SLAC(dot)Stanford(dot)EDU> |
---|---|
To: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | hackers(at)postgreSQL(dot)org, vadim(at)sable(dot)krasnoyarsk(dot)su |
Subject: | Re: Let's talk up 6.3 |
Date: | 1998-03-28 23:04:25 |
Message-ID: | raines-980328150425.A182707@flora04 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>> I am CC'ing Vadim on this. Looks strange. Any way we can reproduce
>> this? Does the removal of the DISTINCT help?
No, removing DISTINCT did not help.
I currently have the data in Oracle and am using Perl and DBI to
transfer data between the two. I did the following additional tests.
I dropped both tables, did a vacuum, and recreated the tables. Run the
subselect with them empty returned no rows as expected. I transfered
over about 20 rows into each table. The subselect ran fine (and fast)
returning the expected result.
I did another drop, vacuum, create and then transfered over the entire
~5500 rows for each table. The subselect now hangs as before. Maybe
it is working if the time is an expotential function of the number of
rows. I killed it after 15 minutes. I fail to see why it should be
much longer than doing the subselect by hand as in my previous email.
Oracle takes a couple of seconds to do the same subselect command.
After killing the postgres process, I reconnected to the database
and tried a vacuum. This also appeared to hang. I killed it after
one minute (it normal took about 5 seconds). I killed the postmaster, then
restarted, reconnected and a vacuum worked fine.
>> Are there a lot of values
>> without the DISTINCT?
There are just as many values as there are values returned by the
subselect. For my example it was just five, but it can certainly
be a lot more for other choices and the DISTINCT is important.
Here are the tables:
bbrmdc=> \d mdc1_runs
Table = mdc1_runs
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| runnum | char() not null | 6 |
| runtype | text | var |
| nevents | int4 | 4 |
| who | text | var |
| note | text | var |
+----------------------------------+----------------------------------+-------+
bbrmdc=> \d mdc1_simu
Table = mdc1_simu
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| runnum | char() not null | 6 |
| version | varchar() not null | 10 |
| jobgrp | varchar() not null | 8 |
| bldrnum | int4 not null | 4 |
| status | text | var |
| cpusecs | int4 | 4 |
| outsize | int4 | 4 |
| machine | text | var |
| location | text | var |
| jobdate | abstime | 4 |
| who | text | var |
| note | text | var |
+----------------------------------+----------------------------------+-------+
I can make the entire database available to you if that would be helpful.
It is about 5MB uncompressed.
pr
--
_________________________________________________________________________
Paul Raines raines(at)slac(dot)stanford(dot)edu 650-926-2369
Stanford Linear Accelerator BABAR Group Software Team
http://www.slac.stanford.edu/~raines/index.html <======== PGP public key
From | Date | Subject | |
---|---|---|---|
Next Message | Gerhard Reithofer | 1998-03-28 23:41:24 | Re: [HACKERS] pgindent on odbc |
Previous Message | Bruce Momjian | 1998-03-28 21:30:20 | Re: Let's talk up 6.3 |