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

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