Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'Gary Stainburn'" <gary.stainburn@ringways.co.uk>,
	<pgsql-sql@postgresql.org>
Subject: Re: Speed up slow select - was gone blind
Date: Thu, 31 Mar 2005 18:14:30 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcU2uFnlDefPcDHGSnyslfBgGkUUrQAySo3g
In-Reply-To: <200504011332.33404.gary.stainburn@ringways.co.uk>
Message-Id: <20050401124711.E00005335E@svr1.postgresql.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, 
	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