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.