Re: its really SLOW!!!!! - Mailing list pgsql-novice

Paul and Michael,

I want to thank you guys so much for your input! What
I ended up doing was creating an index on magnet(masterindex)
and my access time has dropped by a factor of 10! Indexing
really works!!!!! Here's a general question,

In mysql, I can define a non-unique index in the create
table command.

  create table magnet (
     masterindex int,
     current int,
     voltage int,
     key (masterindex)
  );

and the masterindex key could be non-unique. (i.e.
there can be many entries in the magnet table with
the same masterindex value, since masterindex refers
to a unique masterindex value in another table.)

When I went to fill this table, under psql, psql immediately
gave me errors saying that magent(masterindex) was being
filled with identical values. So, is there a way of defining
an index as part of the create table command, or is this
a mysql extention? After reading the chapter on Indexs in
the users guide, there is no mention of creating an index
as part of the create table, but rather an index is
a separate entity used to aid the select command. Am I
getting keys and indexes mixed up? (I assume they are
the same thing....)

Cheers. Steve.

On Tue, 2002-12-03 at 04:52, paul butler wrote:
> From:               "Adler, Stephen" <adler@bnl.gov>
> To:                 Joel Burton <joel@joelburton.com>
> Copies to:          pgsql-novice@postgresql.org
> Subject:            Re: [NOVICE] its really SLOW!!!!!
> Date sent:          02 Dec 2002 17:11:45 -0500
>
> > create master (
> >    masterindex integer not null primary key
> > );
> >
> > create magnet (
> >    masterindex integer,
> >    current integer,
> >    voltage integer
> > );
> >
> > insert into master values (1);
> > insert into master values (1);
> > insert into magnet values (1, 100, 100);
> > insert into magnet values (1, 102, 99);
> > insert into magnet values (1, 99,100);
> > insert into magnet values (2, 100, 101);
> > insert into magnet values (2, 99,103);
> > insert into magnet values (2, 100, 99);
> The first thing you notice is that that there is no primary key for the
> magnet table
> You could do away with the master table by creating
> a new table:
>
> create master2(test serial primary key,magnet integer,voltage
> integer,current integer);
>
> insert into master2(magnet,voltage,current) select
> magnet,voltage,current from magnet;
>
> this takes a few seconds (I'm using pg thru cygwin on an ancient
> p2 machine)
>
> (of course you can maintain integrity on the magnet field by using it
> as  a foreign key referencing the master field, I don't think mysql
> has grasped the importance of foreign keys yet)
>
> The test field will act as a primary key for each set of results and
> give you an unbroken sequence numbered 1-500 000 (or how many
> records there are)
>
> to look at the results of a batch of tests 140001->140500
>
> select * from magnet where test > 140000 limit 500 order by
> magnet
> returns the results
>
>
> Takes no time at all
>
> to look at the results of a particular magnet in this batch
>
> select * from master2 where test > 140000 and test <= 140500 and
> magnet = 1;
>
> Index Scan using master2_pkey on master2  (cost=0.00..16.25
> rows=86 width=16)
>
> NOTICE:  QUERY PLAN:
>
> Index Scan using master2_pkey on master2  (cost=0.00..16.25
> rows=86 width=16)
>
> to look at the results of a particular magnet in this batch
>
> select * from master2 where test > 40000 and test <= 40500 and
> magnet = 1;
>
> to look at the results of a magnets in this batch
>
> select * from master2 where test > 40000 and test <= 40500 and
> magnet in (1,2);
>
> These results come back (subjectively)immediately
>
> Hope this helps
>
> Paul Butler
>
> >
> > The idea being that I have a group of values which
> > are related to the master index. Here I have 2 records
> > in my master table, and 3 groups of values for each
> > master record. The select goes like this
> >
> > select * from magnet where masterindex=1;
> > select * from magnet where masterindex=2;
> >
> > These look ups are very slow. One thing I should remind
> > you guys, is that the master table has over 50,000,
> > the magnet table has about 6 times that many entries,
> > 6 entries for each master record, unlike the example
> > above where I only have 3 entries per record. What I'm
> > wondering is that I need to make masterindex a real
> > index. The idea of course is that I want to do a select
> > on the master table with a join with the magnet table
> > so that I pull out a list of magnet current and voltage
> > settings for each master record. But right now I'm just
> > doing the queries on the magnet table.
> >
> > I hope all of the above makes sense. Cheers. Steve.
> >
> >
> > On Mon, 2002-12-02 at 16:56, Joel Burton wrote:
> > > On Mon, Dec 02, 2002 at 04:43:18PM -0500, Adler, Stephen wrote:
> > > > oh boy, I now realize how little I know about databases...
> > > > OK, so I'm switching from MySQL to postgresql. I have
> > > > this database in MySQL which used the enum data type,
> > > > which looks to be a MySQL extension since there are no
> > > > enum data types in postgresql. What I do is store a bunch
> > > > of values of the setting of a magnet and I have about 6
> > > > entries per master record number. (i.e. I have a table
> > > > which I call the master table, one row entry per master record,
> > > > one table called the magnet table, which has many entries
> > > > per Master record entry.)
> > > > So what I do is loop over the master record entries, say
> > > > record numbers 5000-5300, and for each master record entry,
> > > > I look up in the magnet table data stored for that master
> > > > record. The deal is this, its really slow pulling out the
> > > > data from the magnet table. Like 15 seconds for 500 selects.
> > > > The equivalent select in MySQL ran at least 10 times faster.
> > > >
> > > > Any ideas of what I'm doing wrong? Did I give you guys
> > > > enough information so that you understand what I'm doing?
> > >
> > > Steve --
> > >
> > > Why don't you post a dump of the create statements for your table, and
> > > the select statement that you think is running so slowly?
> > >
> > > --
> > >
> > > Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
> > > Independent Knowledge Management Consultant
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>