Thread: help with query speed
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
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. The testclass is a view not A query so I cannot make an index there. What is the best way to index this stuff? Should I make one index (b-tree) And add all fkeys and pkey from that table? > How many rows are in the tables?
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.
<big snip> > You didn't answer my question about whether they are the same types. Are > they? Yes but testclass is a view. But the field it is referencing is of the same type.
> -----Original Message----- > From: Jason Tesser [mailto:JTesser@nbbc.edu] > Sent: Thursday, February 12, 2004 10:03 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] help with query speed > > > 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 How many rows are in the tables?
> -----Original Message----- > From: Jason Tesser [mailto:JTesser@nbbc.edu] > Sent: Thursday, February 12, 2004 11:33 AM > To: pgsql-general@postgresql.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.
On Thursday 12 February 2004 19:32, Jason Tesser wrote: > Hi > > > Is there an index on: > > testclass.classid > I created the indexes and it speed up a little. Still a little slower > than Access though. Which I think should not be. The testclass is a view > not A query so I cannot make an index there. But you can make one on the underlying table. > What is the best way to index this stuff? Should I make one index > (b-tree) > And add all fkeys and pkey from that table? You should apply indexes on columns where you need to discriminate amongst different values quickly. Anyway - what you need to do here is: 1. Run "VACUUM ANALYZE" 2. Run "EXPLAN ANALYZE SELECT tblroster.transcriptlink ..." and post the output here. Then we can see what query-plan PG is using. 3. Are you happy that your configuration is well tuned? See http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There is a performance tuning article and a breakdown of the config file there. -- Richard Huxton Archonet Ltd
On Thu, 12 Feb 2004, Jason Tesser wrote: > 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. The testclass is a view > not > A query so I cannot make an index there. You didn't answer my question about whether they are the same types. Are they?
Hi
> 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. The testclass is a view
> not
> A query so I cannot make an index there.
You didn't answer my question about whether they are the same types.
Are
they?
Yes they are. I am going to try to get the analyze and vacuum thing
run.
I got an error when I tried to do it. I am the owner of the tables and
Database but I think I need to be owner of some system tables or
Something. Not sure.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
On Thu, 12 Feb 2004, Jason Tesser wrote: > <big snip> > > You didn't answer my question about whether they are the same types. > Are > > they? > > Yes but testclass is a view. But the field it is referencing is of the > same type. OK, assuming they're the same types, what does explain analyze <query> have to say?