Re: problems with postgresql speed

On Mon, 3 Dec 2001, Manuel Trujillo wrote:

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

Eww.

After expanding it, and rewriting it to be a self-join:

SELECT gm.gallery_id, gm.element_id as result_type_id,
gs.element_id AS subevent_id, t.name, v.language_id
FROM gallery_element gm, gallery_element gs,
result_type t, view_subevent_info v
WHERE gm.gallery_id = gs.gallery_id
AND gm.element_type_id = 10
AND gs.element_type_id = 9
AND t.id = gm.element_id
AND v.subevent_id = gs.element_id

This should not only work the same way, but be much faster. Then again,
when you have to resort to something as disgusting as a self-join, I'd
want to guess the schema needs to be denormalized slightly. A certain
amount of replication can intensely speed up and simplify queries.

Then again, I just don't like circular referencing things anyhow, but
that's just me.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas(at)townnews(dot)com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+