Thread: [GENERAL] How to improve query performance?
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
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
>
>
>
On Fri, 19 Feb 1999, Jackson, DeJuan wrote: > 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. Actually, ended up cheating...have a process that runs every hour to update a seperate table to hold the 'cumulative stats' in, vs buildlingit on the fly each time...considering the number of hits I've had on the site since setting it up, having it re-calculate each time could have proven interesting...:) > > -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 > > > > > > > Marc G. Fournier Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org