Performance improvements for 'ichmod -r' (recursive) operations in the
ICAT SQL.  These are based on lots of testing done over the last
couple months using a large ICAT DB (iPlant) on modest hardware.

This is implemented for Postgres only.  It is very likely that the
same temporary table SQL would also speed up Oracle and MySQL ICATs
but we didn't have time to test that at scale so this is only for
Postgres for now.  The SQL for creating the table is probably a bit
different for Oracle and MySQL but otherwise I expect this would work
for them.

The main purpose of these changes are to deal some performance issues
with large instances with lots of access rows, but it seems to perform
just fine at small scales too, i.e. it causes no harm.  Small
instances with moderate numbers of access-control rows performed well
before these changes, and still do.  The overhead of creating the
temporary table seems to be fairly minor, at least in most cases.

The testing was done using the iPlant ICAT DB (thanks to some of the
iPlant folks) running on the the DICE-UCSD srbbrick15 host (a good
server, but fairly old now (there's a lot of that 'going around'...)).

This seemed to be a reasonable testbed though, allowing us DICE-UCSD
folks to reproduce some of the ICAT performance problems that happen
to sites when working with a large instance using a large number of
access-control rows.  This database has over 15,000 users, over
100,000,000 data-objects and nearly 300,000,000 access-control rows.
Even with this very large size, many of the ICAT SQL operations
perform very well (there are over 373 unique SQL forms in the ICAT
code).

This wasn't a perfect testbed though, as significant variations in
timings sometimes occurred.  Overall though, I think we had a fairly
accurate model.  To get to that point tho, we needed to tune Postgres
for our instance.  We added the irods.org page 'Postgresql Tuning'
with some of what we found that might be useful to others.

In addition to timing tests, we also found using 'explain' on
corresponding SQL statements to be useful.

We experimented with various approaches.  It was found that avoiding
certain forms of SQL statements would perform well and we could do
that by doing the recursive aspect of the operations in the ICAT code
itself.  This worked, and was very fast on small sub-collections, but
did not perform well on larger sub-collections, slower in fact than
the old code.

What is implemented here is the use of a temporary table which is
populated and used instead of the much larger source table.  It makes
sense that using a table like this will speed it up, except for the
constraints aspect.  The 'where coll_name = ? or coll_name like ?'
applied on the temporary table isn't really needed (since that table
was populated with constraints like those) but, oddly, does seem to
make it run much faster.  Using 'explain' SQL and test runs confirmed
that it is faster with those constraints.

Appended below are some of the timing results.  newTable is basically
the code committed with this (altho in a 3.3 version of the code):

Collections used:
1 /iplant/home/admin2/wayne1
2 /iplant/home/shared/iplantcollaborative/genomeservices/builds/0.1/Malus_x/domestica_apple/1/sequences/unmasked/chr
3 /iplant/home/shared/iplantcollaborative/genomeservices/builds/0.1
4 /iplant/home/shared/1000bulls

So, for example, I'd run:
time ichmod -r read water157 /iplant/home/shared/1000bulls
time ichmod -r null water157 /iplant/home/shared/1000bulls

Attibutes of those collections:
1: one sub-collection, 29 total files
2: no sub-collections, 144,622 files  (ils -rA takes about 1/2 hour)
3: 944 sub-collections, ils took 25 secs: 415,911 lines of results
4: 12 sub-collections, 295 files

                old code         recursive         newTable
1 to null       1m6.166s            0-2sec           <1sec (1)
1 to read       2m16.766            2-4sec           <1sec (1)
2 to null       1m18.799s          1-11 sec          <1 to 2sec
2 to read       2m48.870s          1m46.059s         <1 to 2sec
3 to null       2m54.229s           1m35 (2)        17-31 sec, 1m28s (2)
3 to read       13m57.473s          8m23            7m50s, 5m46, 12m20
4 to null       1m10                1-2 sec          2-4 sec
4 to read       2m18.292s           1-5 sec          19-29 sec,then 0-5 sec (3)

(1) but sometimes a few seconds, inconsistent
(2) about 4 sec if no change
(3) something seems to get cached as it speeds up as I repeat it more,
    this is inconsistent tho.



git-svn-id: svn://irodssvn.ucsd.edu/trunk@5640 ea137bd6-aa86-47b6-b90d-b101d9f19a90