Thread: Optimizer and Indexes with Joins

Optimizer and Indexes with Joins

From
"Hargis"
Date:
I have a question regarding the optimizer and indexes.   Having a boss that is fluent in data flex but trying to understand postgresql, I am trying to find out if postgresql has a way to basically tell the optimizer which index to use.  The boss states that in data flex you "tell" it to use index 7 and it uses index 7. 
 
We utilize vacuum analyze and we also structure our where clauses to fit the specific indexes.  Further, we set the "enable sequence scan" to off and it did not improve any performace issues.  Most of our selects are "joins" though and that may be causing most of our performance issues.  Any advice out there? 
 
If I need to provide any further information, please advise....
 
TIA,
 
LA Hargis
(Trying to get better at postgres)
 

Re: Optimizer and Indexes with Joins

From
Tom Lane
Date:
"Hargis" <ljhargis@vcn.com> writes:
> I am trying to=
> find out if postgresql has a way to basically tell the optimizer which ind=
> ex to use.

No.

> If I need to provide any further information, please advise....

Specific problem examples would be interesting: queries, table schemas,
EXPLAIN output.

            regards, tom lane

Re: Optimizer and Indexes with Joins

From
Frank Bax
Date:
Sorry, you can't tell pg what index to use.

You can usually get pg to use your indexes by changes to query and/or
schema.  If you post a new message to list, that says "why isn't this query
using index7?", and then include:

1) A copy of your query
2) The output of "explain <query>"
3) schema (you can use "pg_dump -s -t <table> <database>" for each table in
query).

The explanations/suggestions provided by others will quickly teach you a
lot about how pg works in your environment.

Frank

At 10:01 PM 04/03/02 -0700, Hargis wrote:
>I have a question regarding the optimizer and indexes.   Having a boss
>that is fluent in data flex but trying to understand postgresql, I am
>trying to find out if postgresql has a way to basically tell the optimizer
>which index to use.  The boss states that in data flex you "tell" it to
>use index 7 and it uses index 7.
>
>We utilize vacuum analyze and we also structure our where clauses to fit
>the specific indexes.  Further, we set the "enable sequence scan" to off
>and it did not improve any performace issues.  Most of our selects are
>"joins" though and that may be causing most of our performance
>issues.  Any advice out there?
>
>If I need to provide any further information, please advise....
>
>TIA,
>
>LA Hargis
>(Trying to get better at postgres)
>