From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Speed up slow select - was gone blind
Date: Tue, 5 Apr 2005 10:44:31 +0100
Message-ID: <200504051044.31806.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <b918cf3d050401044677139922@mail.gmail.com>
References: <200504011332.33404.gary.stainburn@ringways.co.uk>
<b918cf3d050401044677139922@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Speed up slow select - was gone blind
In-Reply-To: <200504051044.31806.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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