Re: Let's talk up 6.3 - Mailing list pgsql-hackers

bbrmdc=> select distinct runtype from mdc1_runs where runnum in
bbrmdc-> (select runnum from mdc1_simu where version = '3.1.0');

Ok, without indices, the subselect took 35 seconds.  I then
created the following two indices which seem to correspond
to the ones I have in Oracle:

bbrmdc=> create index mdc1_runs_pk on mdc1_runs using btree (
bbrmdc-> runnum char_ops );
CREATE
bbrmdc=> create index mdc1_simu_pk on mdc1_simu using btree (
bbrmdc-> runnum char_ops, version varchar_ops );
CREATE

The subselect still took 35 seconds.  I then created:

bbrmdc=> create index mdc1_simu_ver on mdc1_simu using btree (
bbrmdc-> version varchar_ops );
CREATE

Now the subselect takes < 3 seconds.  Should I have expected that
second index above to help at all?  Since all runnum's are
unique in this example, probably not.  Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

>> Having it crash is certainly not an acceptable outcome, but I am sure
>> indexes will fix the problem.
>>

Well, it didn't exactly crash.  I just gave up on it and killed it
myself after 15 minutes.  That was when I had about 5500 rows in
each table rather than the 2500 now.  BTW, is there anyway for a "user"
to stop a runaway postgres process?  I had to log in directly to the
server and kill it as either root or postgres.

>> Now, the fact that it runs quickly as separate queries, even without the
>> indexes, but takes a long time with the indexes, I think is
>> understandable.  Think of a join of two tables.  You can do through each
>> quickly, but if you join two non-indexed fields, it will take quite some
>> time.  I think our subselect code is doing just that.  We designed it
>> that way to give good performance for the majority of subselects,
>> including correlated ones.
>>

Is there a better way to do this subselect?  Is there a way to
save the results of one query and feed it into a second one easily
when doing interactive stuff on psql?  I know this can be done in
programming, though I worry the statement might get too long. I
was thinking of trying a function for this but they only seem to
return scalars, not suitable for a IN clause.

On another note, is there anyway to prevent a user from being able
to create tables in a database?  There only seems to be security
in making the connection in the first place and then there is
just security on existing tables.  I want to set up a "safe" user
id that has query access only on a database.

Thanks for all your help.

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