pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Speed up slow select - was gone blind
5+ messages / 4 participants
[nested] [flat]
* Speed up slow select - was gone blind
@ 2005-04-01 12:32 Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-31 12:44 ` Re: Speed up slow select - was gone blind Dinesh Pandey <dpandey@secf.com>
2005-04-01 12:46 ` Re: Speed up slow select - was gone blind Mike Rylander <mrylander@gmail.com>
2005-04-01 13:00 ` Re: Speed up slow select - was gone blind Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 5+ messages in thread
From: Gary Stainburn @ 2005-04-01 12:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed up slow select - was gone blind
2005-04-01 12:32 Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-31 12:44 ` Dinesh Pandey <dpandey@secf.com>
2 siblings, 0 replies; 5+ messages in thread
From: Dinesh Pandey @ 2005-03-31 12:44 UTC (permalink / raw)
To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed up slow select - was gone blind
2005-04-01 12:32 Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-04-01 12:46 ` Mike Rylander <mrylander@gmail.com>
2005-04-05 09:44 ` Re: Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 5+ messages in thread
From: Mike Rylander @ 2005-04-01 12:46 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed up slow select - was gone blind
2005-04-01 12:32 Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-04-01 12:46 ` Re: Speed up slow select - was gone blind Mike Rylander <mrylander@gmail.com>
@ 2005-04-05 09:44 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2005-04-05 09:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed up slow select - was gone blind
2005-04-01 12:32 Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-04-01 13:00 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-04-01 13:00 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-05 09:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-01 12:32 Speed up slow select - was gone blind Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-31 12:44 ` Dinesh Pandey <dpandey@secf.com>
2005-04-01 12:46 ` Mike Rylander <mrylander@gmail.com>
2005-04-05 09:44 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-04-01 13:00 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox