RE: [GENERAL] How to improve query performance?
От | Jackson, DeJuan |
---|---|
Тема | RE: [GENERAL] How to improve query performance? |
Дата | |
Msg-id | F10BB1FAF801D111829B0060971D839F68CBAF@cpsmail обсуждение исходный текст |
Ответ на | [GENERAL] How to improve query performance? (The Hermit Hacker <scrappy@hub.org>) |
Ответы |
RE: [GENERAL] How to improve query performance?
|
Список | pgsql-general |
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 > > >
В списке pgsql-general по дате отправления: