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