From: | "Dann Corbit" <DCorbit(at)connx(dot)com> |
---|---|
To: | "Jason Tesser" <JTesser(at)nbbc(dot)edu>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: help with query speed |
Date: | 2004-02-12 20:51:17 |
Message-ID: | D90A5A6C612A39408103E6ECDD77B829408CFC@voyager.corporate.connx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: Jason Tesser [mailto:JTesser(at)nbbc(dot)edu]
> Sent: Thursday, February 12, 2004 11:33 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] help with query speed
>
>
> Hi
>
> > > I have the below query written. I have removed a lot from the
> select
> > > Statement for simplicity sake. The query takes way too
> long. I am
> > > moving from an Access backend to a Postgres back with
> Access in the
> > > front. The below query is taking like 14-20 seconds. Is their a
> > > better way I can write the joins. I would think that Postgres
> > > should be fast than Access. BTW I am writing these as
> Pass through
> > > queries so it is not a problem with Access. I have Even ran the
> > > query directly against Postgres.
> > >
> > > SELECT
> > > tblroster.transcriptlink
> > > FROM
> > > tblroster
> > > FULL OUTER JOIN testclass ON (tblroster.classlink =
> > > testclass.classid)
> > > FULL OUTER JOIN tblqualitypoint ON (tblroster.lettergrade =
> > > tblqualitypoint.letter)
> > > ORDER BY
> > > tblroster.transcriptlink
> >
> > Is there an index on:
> > tblroster.classlink
> >
> > Is there an index on:
> > testclass.classid
> >
> > Is there an index on:
> > tblroster.lettergrade
> >
> > Is there an index on:
> > tblqualitypoint.letter
> >
> > Is there an index on:
> > tblroster.transcriptlink
>
> I created the indexes and it speed up a little. Still a
> little slower than Access though. Which I think should not
> be.
Try this experiment:
Take your MS Access database, and start a big transaction. Right in the
middle of the transaction, yank the plug on your computer out of the
wall. Then, take a baseball bat and bash your computer to smithereens.
It won't prove anything, but it will be a fun exercise.
;-)
>The testclass is a view not
> A query so I cannot make an index there.
But you can make an index on the underlying tables.
> What is the best way to index this stuff?
There is no simple answer.
> Should I make one index
> (b-tree)
> And add all fkeys and pkey from that table?
The foreign and primary keys won't make any difference in speed. They
are for enforcing RI.
> > How many rows are in the tables?
This is important.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-02-12 21:09:18 | Mysql to Postgresql |
Previous Message | Richard Huxton | 2004-02-12 20:50:48 | Re: help with query speed |