Thread: How to improve query performance?
I did up an online survey over the weekend, and its gotten a little on
the...slow side :( Unfortunately, I can see where I can speed it up any,
so I'm asking for any suggestions, if its possible.
Explain on the query I'm using shows:
Sort (cost=5455.34 size=0 width=0)
-> Aggregate (cost=5455.34 size=0 width=0)
-> Group (cost=5455.34 size=0 width=0)
-> Sort (cost=5455.34 size=0 width=0)
-> Seq Scan on op_sys (cost=5455.34 size=39024 width=12)
The Query itself is:
my $OSlisting = "\
select count(sys_type) as tot_sys_type,sys_type \
from op_sys \
where sys_type is not null \
group by sys_type \
order by tot_sys_type desc;";
The table looks like:
Table = op_sys
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| ip_number | text | var |
| sys_type | text | var |
| browser_type | text | var |
| entry_added | datetime | 8 |
| probe | bool | 1 |
+----------------------------------+----------------------------------+-------+
Indices: op_sys_ip
op_sys_type
The table holds ~120k records right now, and the above query returns ~1100.
To get a feel for the speed it returns, see http://www.hub.org/OS_Survey
I can't think of any way to improve the speed, and yes, I do a 'vacuum
analyze' on it periodically (did one just before the above EXPLAIN)...
Other other note...its a v6.4.2 server, running on a PII with 384Meg of
RAM and FreeBSD 3.0-STABLE...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker wrote: > > The table holds ~120k records right now, and the above query returns ~1100. > > To get a feel for the speed it returns, see http://www.hub.org/OS_Survey > > I can't think of any way to improve the speed, and yes, I do a 'vacuum > analyze' on it periodically (did one just before the above EXPLAIN)... > > Other other note...its a v6.4.2 server, running on a PII with 384Meg of > RAM and FreeBSD 3.0-STABLE... What is -S (memory for sorting)? Try to use -S 8192 or -S 16384. Vadim
On Tue, 16 Feb 1999, The Hermit Hacker wrote:
Wouldn't it be faster if you didn't do all those text fields?
It'd certainly be smaller. OS could be an integer, along with browser.
Also, there's a type in Postgres for IP address, might as well use it. :)
One thing that you could do to speed it up a *LOT* is to build a
statistics table, and load it all up in that ahead of time, and have a
trigger to keep the other table up-to-date while you're updating.
// I did up an online survey over the weekend, and its gotten a little on
// the...slow side :( Unfortunately, I can see where I can speed it up any,
// so I'm asking for any suggestions, if its possible.
//
// Explain on the query I'm using shows:
//
// Sort (cost=5455.34 size=0 width=0)
// -> Aggregate (cost=5455.34 size=0 width=0)
// -> Group (cost=5455.34 size=0 width=0)
// -> Sort (cost=5455.34 size=0 width=0)
// -> Seq Scan on op_sys (cost=5455.34 size=39024 width=12)
//
// The Query itself is:
//
// my $OSlisting = "\
// select count(sys_type) as tot_sys_type,sys_type \
// from op_sys \
// where sys_type is not null \
// group by sys_type \
// order by tot_sys_type desc;";
//
// The table looks like:
//
// Table = op_sys
// +----------------------------------+----------------------------------+-------+
// | Field | Type | Length|
// +----------------------------------+----------------------------------+-------+
// | ip_number | text | var |
// | sys_type | text | var |
// | browser_type | text | var |
// | entry_added | datetime | 8 |
// | probe | bool | 1 |
// +----------------------------------+----------------------------------+-------+
// Indices: op_sys_ip
// op_sys_type
//
// The table holds ~120k records right now, and the above query returns ~1100.
//
// To get a feel for the speed it returns, see http://www.hub.org/OS_Survey
//
// I can't think of any way to improve the speed, and yes, I do a 'vacuum
// analyze' on it periodically (did one just before the above EXPLAIN)...
//
// Other other note...its a v6.4.2 server, running on a PII with 384Meg of
// RAM and FreeBSD 3.0-STABLE...
//
//
// Marc G. Fournier
// Systems Administrator @ hub.org
// primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
//
//
//
--
Principal Member Technical Staff, beyond.com The world is watching America,
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L______________________________________________ and America is watching TV. __
On Tue, 16 Feb 1999, Vadim Mikheev wrote: > The Hermit Hacker wrote: > > > > The table holds ~120k records right now, and the above query returns ~1100. > > > > To get a feel for the speed it returns, see http://www.hub.org/OS_Survey > > > > I can't think of any way to improve the speed, and yes, I do a 'vacuum > > analyze' on it periodically (did one just before the above EXPLAIN)... > > > > Other other note...its a v6.4.2 server, running on a PII with 384Meg of > > RAM and FreeBSD 3.0-STABLE... > > What is -S (memory for sorting)? Try to use -S 8192 or -S 16384. Not sure if/how much that improved it, since I didn't time previously, but down to ~1 minute...still a long time, but...should tied me over... Thanks... Marc G. Fournier Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Mon, 15 Feb 1999, dustin sallings wrote:
> On Tue, 16 Feb 1999, The Hermit Hacker wrote:
>
> Wouldn't it be faster if you didn't do all those text fields?
> It'd certainly be smaller. OS could be an integer, along with browser.
> Also, there's a type in Postgres for IP address, might as well use it. :)
Not sure how any of this would make things faster...I'd almost
think that the extra joins required to map # to name would increase
things...
> One thing that you could do to speed it up a *LOT* is to build a
> statistics table, and load it all up in that ahead of time, and have a
> trigger to keep the other table up-to-date while you're updating.
Thought of this one, but must be missing something in my 'insert
into...select from' statement (see -hackers)...I'm going to have to look
into re-structuring it, and see if I can improve speeds more...getting rid
of the 'text' fields should help things someone, just not sure by how
much...
> // I did up an online survey over the weekend, and its gotten a little on
> // the...slow side :( Unfortunately, I can see where I can speed it up any,
> // so I'm asking for any suggestions, if its possible.
> //
> // Explain on the query I'm using shows:
> //
> // Sort (cost=5455.34 size=0 width=0)
> // -> Aggregate (cost=5455.34 size=0 width=0)
> // -> Group (cost=5455.34 size=0 width=0)
> // -> Sort (cost=5455.34 size=0 width=0)
> // -> Seq Scan on op_sys (cost=5455.34 size=39024 width=12)
> //
> // The Query itself is:
> //
> // my $OSlisting = "\
> // select count(sys_type) as tot_sys_type,sys_type \
> // from op_sys \
> // where sys_type is not null \
> // group by sys_type \
> // order by tot_sys_type desc;";
> //
> // The table looks like:
> //
> // Table = op_sys
> // +----------------------------------+----------------------------------+-------+
> // | Field | Type | Length|
> // +----------------------------------+----------------------------------+-------+
> // | ip_number | text | var |
> // | sys_type | text | var |
> // | browser_type | text | var |
> // | entry_added | datetime | 8 |
> // | probe | bool | 1 |
> // +----------------------------------+----------------------------------+-------+
> // Indices: op_sys_ip
> // op_sys_type
> //
> // The table holds ~120k records right now, and the above query returns ~1100.
> //
> // To get a feel for the speed it returns, see http://www.hub.org/OS_Survey
> //
> // I can't think of any way to improve the speed, and yes, I do a 'vacuum
> // analyze' on it periodically (did one just before the above EXPLAIN)...
> //
> // Other other note...its a v6.4.2 server, running on a PII with 384Meg of
> // RAM and FreeBSD 3.0-STABLE...
> //
> //
> // Marc G. Fournier
> // Systems Administrator @ hub.org
> // primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> //
> //
> //
>
> --
> Principal Member Technical Staff, beyond.com The world is watching America,
> pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
> | Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
> L______________________________________________ and America is watching TV. __
>
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org