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@townnews.com    AIM  : trifthen                      |
| Web  : hamster.lee.net                                              |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+