Re: help with query speed

On Thu, 12 Feb 2004, Jason Tesser wrote:

> 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

Are tblroster.classlink and testclass.classid the same type and indexed?

Same for tblroster.lettergrade and tblqualitypoint.letter

Also is tblroster.transcriptlink indexed?  Is the query using indexes?
Check it with

explain analyze <query>

and see what it says, look for big differences between expected and actual
rows, width, and loops.