FW: problems with postgresql speed

Manuel wrote:
>
> Hi.
>
> I've serious problems with the speed of my database.
> If I execute any query like this;
>
[big ugly query :]


Yes I'd say so, that's a rather horrendous query. You're a better computer
than I if you can make heads or tails of it :). So I ran it through an SQL
tidier and massaged it a bit.

SELECT   d.gallery_id, e.subevent_id, d.result_type_id,
         d.result_type_name, e.language_id
FROM     (
            SELECT   g.gallery_id, g.element_id AS result_type_id,
                     r.name AS result_type_name
            FROM     gallery_element g, result_type r
            WHERE    r.id = g.element_id
              AND    g.element_type_id = 10
         ) d
         JOIN
         (
            SELECT   g.gallery_id, g.element_id AS subevent_id,
                     v.language_id
            FROM     gallery_element g, view_subevent_info v
            WHERE    v.subevent_id = g.element_id
              AND    g.element_type_id = 9
         ) e
         ON d.gallery_id = e.gallery_id;

A few suggestions:

- I get the feeling the table are't very normalised. I could be wrong but
the fact your combining two records from the same table (using subtables)
into one 'seems' odd.

- A self join might be a better fit in this case, and probably much less
expensive than the subqueries.

- As stated the subtables are going to be very expensive, use EXAMINE (see
manual) to check which pieces are worst and try optimising there first.

- Join using a WHERE a.pk = b.fk syntax to let Pg's engine optimise (general
rule).

- 'view_subevent_info' I'm guessing this is a VIEW (for obvious reasons).
You use only a single field from it, depending on the views complexity you
could be adding tons of work. Maybe get it from the source table.

- Possibly make this query a view or even a temp/cache table if it's called
often.

I'm disinclined to think Pg is the most limiting factor in this, especially
as the query looks to be generated by drag and drop tool. A bit of human
mucking about with the SQL might yield better results.



Also check out Bruce Momjian chapters on performance tuning (a great
purchase).

Perfomance
http://www.ca.postgresql.org/docs/aw_pgsql_book/node106.html

Hardware Performance Tuning
http://www.ca.postgresql.org/docs/momjian/hw_performance/