Обсуждение: Speed up slow select - was gone blind
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, dealershipsd, 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_descas 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
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
Try with creating INDEX on the used tables...It will make your search query faster. Thanks Dinesh Pandey -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Gary Stainburn Sent: Friday, April 01, 2005 6:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] Speed up slow select - was gone blind 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, dealershipsd, 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_descas 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
Gary Stainburn 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? You'll want to compare the output of EXPLAIN ANALYSE for each version. Post them here, or on the performance list. Also, make sure your basic PG tuning is ok. http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd
Hi folks. I did send an explain analyze last week but for some reason it didn't appear on the list. However, I've looked into the delay and it doesn't seem to be the SQL. I'm now looking into why my PHP seems to sit there for 20+ seconds doing nowt. Thanks to everyone for the help anyway. Gary On Friday 01 Apr 2005 1:46 pm, you wrote: > 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 -- 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