Re: Let's talk up 6.3 - Mailing list 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@slac.stanford.edu           650-926-2369
Stanford Linear Accelerator   BABAR Group                   Software Team
http://www.slac.stanford.edu/~raines/index.html  <======== PGP public key