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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
> //
> //
> //
>
> --
> Principal Member Technical Staff, beyond.com The world is watching America,
> pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org