Re: Speed up slow select - was gone blind - Mailing list pgsql-sql

Can you send the EXPLAIN ANALYZE of each?  We can't really tell where
the slowdown is without that.

On Apr 1, 2005 12:32 PM, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks.
> 
> I've got my select working now, but I haven't received the speed
> increase I'd expected.  It replaced an earlier select which combined a
> single explicit join with multiple froms.
> 
> The first select is the old one, the second  one is the new one (with a
> new join).  The new one takes 24 seconds to run while the old one took
> 29.
> 
> How can I redo the select to improve the speed, or what else can I do to
> optimaise the database?
> 
> original (ugly)
> ~~~~~
> 
> SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,
>         r.r_pack_mats, r.r_delivery,
>         (date(r.r_delivery) - date(now())) AS r_remaining,
>         r.r_created, r.r_completed, r.r_salesman, r.salesman_name,
>         d.d_des, de.de_des,
>         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
>         t.t_id, t.t_des,
>         s.s_id, s.s_des,
>         c.c_id, c.c_des,
>         co.com_count, co.com_unseen
> FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id,
>         r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle,
>         r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman,
>         r.r_created, r.r_completed, r.r_u_id,
>         u.u_username AS salesman_name
>         FROM (requests r LEFT JOIN users u ON
>                 ((r.r_salesman = u.u_id)))) r,
>         users u,
>         request_types t,
>         request_states s,
>         dealerships d,
>         departments de,
>         customers c,
>         comment_tallies co
> WHERE   (r.r_d_id = d.d_id) AND
>         (r.r_s_id = s.s_id) AND
>         (r.r_c_id = c.c_id) AND
>         (r.r_t_id = t.t_id) AND
>         (r.r_d_id = d.d_id) AND
>         (r.r_de_id = de.de_id) AND
>         (r.r_u_id = u.u_id) AND
>         (r.r_id = co.r_id))
> ORDER BY r.r_id;
> 
> new
> ~~~
> SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,
> r.r_pack_mats, r.r_delivery,
>         (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created,
> r.r_completed, r.r_salesman,
>         sm.u_username as salesman_name,
>         d.d_des, de.de_des,
>         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
>         t.t_id, t.t_des,
>         s.s_id, s.s_des,
>         c.c_id, c.c_des,
>         co.com_count, co.com_unseen,
>         pl.pl_id, pl.pl_desc as plates
> FROM requests r
> left outer join users sm on sm.u_id = r.r_salesman
> left outer join users u on r.r_u_id = u.u_id
> left outer join request_types t on r.r_t_id = t.t_id
> left outer join request_states s on r.r_s_id = s.s_id
> left outer join dealerships d on r.r_d_id = d.d_id
> left outer join departments de on r.r_de_id = de.de_id
> left outer join customers c on r.r_c_id = c.c_id
> left outer join comment_tallies co on r.r_id = co.r_id
> left outer join plates pl on r.r_plates = pl.pl_id
> ORDER BY r.r_id;
> 
> --
> Gary Stainburn
> 
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org