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

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 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