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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
//
//
//

--
Principal Member Technical Staff, beyond.com The world is watching America,
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)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. __