Skip site navigation (1) Skip section navigation (2)

Re: Let's talk up 6.3

From: Paul Raines <raines(at)SLAC(dot)Stanford(dot)EDU>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: vadim(at)sable(dot)krasnoyarsk(dot)su, hackers(at)postgreSQL(dot)org
Subject: Re: Let's talk up 6.3
Date: 1998-03-30 17:54:50
Message-ID: raines-980330095450.A212707@flora04 (view raw, whole thread or download thread mbox)
Lists: 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 );
bbrmdc=> create index mdc1_simu_pk on mdc1_simu using btree (
bbrmdc-> runnum char_ops, version varchar_ops );

The subselect still took 35 seconds.  I then created:

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

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.


Paul Raines               raines(at)slac(dot)stanford(dot)edu           650-926-2369
Stanford Linear Accelerator   BABAR Group                   Software Team  <======== PGP public key

In response to


pgsql-hackers by date

Next:From: Zeugswetter Andreas SARZDate: 1998-03-30 18:19:01
Subject: AW: [HACKERS] Re: Let's talk up 6.3
Previous:From: Bruce MomjianDate: 1998-03-30 17:45:38
Subject: Re: [HACKERS] found another overrun

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group