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@slac.stanford.edu 650-926-2369
Stanford Linear Accelerator BABAR Group Software Team
http://www.slac.stanford.edu/~raines/index.html <======== PGP public key

Paul Raines wrote:

I have made no indices yet. And these are the only two tables
in the database (beside the system ones).

bbrmdc=> explain verbose select distinct runtype from mdc1_runs where runnum in
bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');

Unique (cost=686.02 size=0 width=0)
-> Sort (cost=686.02 size=0 width=0)
-> Seq Scan on mdc1_runs (cost=686.02 size=1455 width=12)
SubPlan
-> Seq Scan on mdc1_simu (cost=733.02 size=1 width=12)

Current implementation of IN is very simple. As you see from EXPLAIN
for each row from mdc1_runs server performes SeqScan on mdc1_simu.
Try to create index on mdc1_simu (version) and let's know about results.
Also, you could create index on mdc1_simu (version, runnum) and re-write
your query as

select distinct runtype from mdc1_runs where
EXISTS (select * from mdc1_runs where version = '...' and
runnum = mdc1_runs.runnum);

- this can be faster.

In the future, subselects in FROM-clause will be implemented and
'IN' and others 'Op ANY' will be handled in this new way.

Vadim

I have made no indices yet. And these are the only two tables
in the database (beside the system ones).

No indexes. No wonder it takes so long. Put an index on
mdc1_runs.runnum and mdc1_simu.version, and see how fast it is. Did
Oracle manage to do this quickly without the indexes?

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

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.

bbrmdc=> explain verbose select distinct runtype from mdc1_runs where runnum in
bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');
NOTICE: QUERY PLAN:

Unique (cost=686.02 size=0 width=0)
-> Sort (cost=686.02 size=0 width=0)
-> Seq Scan on mdc1_runs (cost=686.02 size=1455 width=12)
SubPlan
-> Seq Scan on mdc1_simu (cost=733.02 size=1 width=12)

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

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

Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

For PostgreSQL this is currently true, since indexes are currently not
used for order by. If you have a unique first column in an index,
then all following columns could only be used for sorting,
not for faster access (access actually gets worse).

Andreas

Paul Raines wrote:

Current implementation of IN is very simple. As you see from EXPLAIN
for each row from mdc1_runs server performes SeqScan on mdc1_simu.
Try to create index on mdc1_simu (version) and let's know about results.
Also, you could create index on mdc1_simu (version, runnum) and re-write
your query as

select distinct runtype from mdc1_runs where
EXISTS (select * from mdc1_runs where version = '...' and
runnum = mdc1_runs.runnum);

- this can be faster.

It was about 4 seconds faster. After creating the indices, the
above took < 3 seconds, as did the original subselect statement.

Please remember us how long query was in Oracle.
Also, as I understand, subselect with EXISTS takes < 3 sec and
original subselect (with IN) takes ~ 7 sec - is this correct ?

Vadim

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

This index is useless. If you are only restricting on the second field
of an index, and not the first, the index is useless, just like knowing
the second letter of a word is q doesn't help you look it up in a
dictionary.

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?

See above.

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

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.

No, but on the TODO list.

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.

SELECT * INTO TABLE ... would work. DELETE when done.

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.

No, again on the TODO list.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

For PostgreSQL this is currently true, since indexes are currently not
used for order by. If you have a unique first column in an index,
then all following columns could only be used for sorting,
not for faster access (access actually gets worse).

Sorry, don't follow this logic. He is not restricting on the first
field of the index, so the index is not used.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Andreas:

Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

For PostgreSQL this is currently true, since indexes are currently not
used for order by. If you have a unique first column in an index,
then all following columns could only be used for sorting,
not for faster access (access actually gets worse).

The rule 'if the first attribute of an index is unique, then additional
attributes are basically useless' is exactly correct for all systems, not
just PostgreSQL. It has nothing to do with whether indexes are used for
'orderby'.

A bit of thought will reveal that if the first key is unique then there
is no way any subsequent key can influence the sort order. Consider:

col1 col2
---- ----
A 9
B 5
C 1
... ...

There is no value you can put in col2 that will make 'A' sort after 'B'.

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Of course, someone who knows more about this will correct me if I'm wrong,
and someone who knows less will correct me if I'm right."
--David Palmer (palmer@tybalt.caltech.edu)

Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

For PostgreSQL this is currently true, since indexes are

currently not

used for order by. If you have a unique first column in an index,
then all following columns could only be used for sorting,
not for faster access (access actually gets worse).

Sorry, don't follow this logic. He is not restricting on the first
field of the index, so the index is not used.

Ooops, I did not look at that, I just took the sentence standalone,
and under the presumption that the first field of the index is in the where
restriction.

Andreas

Would a rule be that
if the first attribute of an index is unique, then additional
attributes are basically useless?

<< cut some of my nonsense here >>

David Gould writes:

The rule 'if the first attribute of an index is unique, then

additional

attributes are basically useless' is exactly correct for all

systems, not

just PostgreSQL. It has nothing to do with whether indexes are used

for

'orderby'.

The second Ooops in one day, I guess I have to relearn the meaning of
"First think then talk.", Sorry. (what a day, well at least the sun is
shining here :-)
But to speak in self defense there is still one case where the trailing
columns in this index would help. If only index columns are selected,
the engine could do an index only scan (not in postgresql yet).

Andreas

bbrmdc=> create index mdc1_simu_pk on mdc1_simu using btree (
bbrmdc-> runnum char_ops );

^^^^^^^^
bpchar_ops must be used !!!
char_ops is for 'char' data type, not for 'char(N)'.
But it's much better to DON'T USE ANY XXX_ops at all (and USING btree too -
btree is default) - both features aren't standard and useless in your case.

Okay, I destroyed the database and recreated it. I then created
the following tables and indices;

bbrmdc=> create table mdc1_simu (
bbrmdc-> runnum char(6) not null,
bbrmdc-> version varchar(10) not null,
bbrmdc-> jobgrp varchar(8) not null,
bbrmdc-> bldrnum int4 not null,
bbrmdc-> status text,
bbrmdc-> cpusecs int4,
bbrmdc-> outsize int4,
bbrmdc-> machine text,
bbrmdc-> location text,
bbrmdc-> jobdate abstime,
bbrmdc-> who text,
bbrmdc-> note text );
CREATE
bbrmdc=> create table mdc1_runs (
bbrmdc-> runnum char(6) not null,
bbrmdc-> runtype text,
bbrmdc-> nevents int4,
bbrmdc-> who text,
bbrmdc-> note text );
CREATE
bbrmdc=> create unique index mdc1_runs_pk on mdc1_runs ( runnum );
CREATE
bbrmdc=> create index mdc1_simu_pk on mdc1_simu ( runnum );
CREATE
bbrmdc=> create index mdc1_simu_ver on mdc1_simu ( version );
CREATE

I then filled the tables from my Perl DBI script copying Oracle
data to Postgres (same as before). This time, it worked without
failing do the index FATAL.

I immediatetly tried my subselect.

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

After a couple of minutes, I killed the postgres process. I quit my
psql and then reconnectd. I tried a simple select and it hung too.
Killed it and reconnected. I dropped the three indices and tried a
vacuum. It also hung forever. I killed the postgres process,
restarted the postmaster, deleted the pg_vlock file, and retried the
vacuum. It worked. A simple select then works too.

I recreated the indices exactly as above, and selects still
work. The subselect also worked too and took about 12 seconds.

I destroyed the database and started over. This time, after
transfering the data, I first tried a simple select. It worked
fine. Then the subselect. It hung again. Killed and reconnected.
A simple select also hangs. Killed it, restarted the postmaster,
reconnected and did a vacuum. Now both simple select and
subselect work fine.

Any clues?

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

Paul Raines wrote:

Okay, I destroyed the database and recreated it. I then created
the following tables and indices;

...

I then filled the tables from my Perl DBI script copying Oracle
data to Postgres (same as before). This time, it worked without
failing do the index FATAL.

I immediatetly tried my subselect.

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

After a couple of minutes, I killed the postgres process. I quit my
psql and then reconnectd. I tried a simple select and it hung too.
Killed it and reconnected. I dropped the three indices and tried a
vacuum. It also hung forever. I killed the postgres process,
restarted the postmaster, deleted the pg_vlock file, and retried the
vacuum. It worked. A simple select then works too.

First, I assume that you didn't run vacuum after filling tables and so
indices were not used: to get index scans you have to either create
indices _after_ (not before) filling tables or vacuum tables _after_
filling.

Second, after killing server process it's better to restart postmaster!
Killing is abnormal thing - some locks/spinlocks were not released
and so your next connection hung.

I recreated the indices exactly as above, and selects still
work. The subselect also worked too and took about 12 seconds.

What's Oracle time ?

Vadim