Re: Let's talk up 6.3

>> 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