From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to improve query performance? |
Date: | 1999-02-16 06:00:47 |
Message-ID: | Pine.BSF.4.05.9902160154390.10449-100000@thelab.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim Mikheev | 1999-02-16 06:10:23 | Re: [GENERAL] How to improve query performance? |
Previous Message | Clark Evans | 1999-02-16 04:29:48 | How about a contract? (Was: Re: [GENERAL] A book for PgSQL? A need? yes? no?) |