Re: optimizing query

(Replying to general and performance in a hope to move this
to performance after a couple of replies).

On Wed, 22 Jan 2003, Chantal Ackermann wrote:

> I am getting the following output from EXPLAIN, concerning a query with
> joins. The merge uses index scans but takes too long, in my opinion. The
> query is in fact only a part (subquery) of another one, but it is the
> bottle neck.
>
> As I am quite ignorant in optimizing queries, and I have no idea where
> to find documentation on the net on how to learn optimizing my queries,
> I am posting this here in hope someone will give me either tips how to
> optimize, or where to find some tutorial that could help me get along on
> my own.
>
> dropping the "DISTINCT" has some effect, but I can't really do without.

The first thing is, have you done ANALYZE recently to make sure that the
statistics are correct and what does EXPLAIN ANALYZE give you (that will
run the query and give the estimate and actual). Also, if you haven't
vacuumed recently, you may want to vacuum full.

How many rows are there on gene, disease and both occurrances tables?
I'd wonder if perhaps using explicit sql join syntax (which postgres uses
to constrain order) to join disease and disease_occurrences_puid before
joining it to the other two would be better or worse in practice.