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

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