RE: [GENERAL] How to improve query performance?

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