How to improve query performance?

Started by The Hermit Hackerabout 27 years ago7 messagesgeneral

scrappy@hub.org

about 27 years ago

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

Vadim Mikheev

vadim@krs.ru

about 27 years ago

In reply to: The Hermit Hacker (#1)

Re: [GENERAL] How to improve query performance?

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

Dustin Sallings

dustin@spy.net

about 27 years ago

In reply to: The Hermit Hacker (#1)

Re: [GENERAL] How to improve query performance?

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. __

The Hermit Hacker

scrappy@hub.org

about 27 years ago

In reply to: Vadim Mikheev (#2)

Re: [GENERAL] How to improve query performance?

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

The Hermit Hacker

scrappy@hub.org

about 27 years ago

In reply to: Dustin Sallings (#3)

Re: [GENERAL] How to improve query performance?

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

Jackson, DeJuan

djackson@cpsgroup.com

about 27 years ago

In reply to: The Hermit Hacker (#5)

RE: [GENERAL] How to improve query performance?

The only suggestion I have is to do the Sort after you get the data
back, Perl's pretty good at that.

Let me know what the timings are. I went to the site and it looks like
it only take ~3-5 seconds to get the data to my browser and format it.

-DEJ

Show quoted text

-----Original Message-----
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

Import Notes

Resolved by subject fallback

The Hermit Hacker

scrappy@hub.org

about 27 years ago

In reply to: Jackson, DeJuan (#6)

RE: [GENERAL] How to improve query performance?

On Fri, 19 Feb 1999, Jackson, DeJuan wrote:

The only suggestion I have is to do the Sort after you get the data
back, Perl's pretty good at that.

Let me know what the timings are. I went to the site and it looks like
it only take ~3-5 seconds to get the data to my browser and format it.

Actually, ended up cheating...have a process that runs every hour to
update a seperate table to hold the 'cumulative stats' in, vs buildlingit
on the fly each time...considering the number of hits I've had on the site
since setting it up, having it re-calculate each time could have proven
interesting...:)

-DEJ
-----Original Message-----
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

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org