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