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