From: | Lukas Ertl <l(dot)ertl(at)univie(dot)ac(dot)at> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Query performance question |
Date: | 2001-03-28 20:23:27 |
Message-ID: | Pine.BSF.4.33.0103282214470.504-100000@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
I'm having trouble with an obviously simple query that just doesn't
perform quite good IMO.
I have two tables:
httplog=# \d hits
Table "hits"
Attribute | Type | Modifier
-------------+-----------+-----------------------------------------------
id | integer | not null default nextval('hits_id_seq'::text)
page_id | integer | not null
referrer_id | integer | not null
ip_addr | inet | not null
time | timestamp | not null
domain_id | integer | not null
Index: hits_pkey
httplog=# \d referrer
Table "referrer"
Attribute | Type | Modifier
-----------+--------------+----------
id | integer |
url | varchar(300) |
Index: referrer_pkey
These are part of an HTTP-log database. Table 'hits' has about 7000
rows, table 'referrer' has about 350 rows. Now I want to know what the top
ten referrers are, and I issue this query:
SELECT count(*), url FROM hits, referrer WHERE referrer.id = referrer_id
GROUP BY url ORDER BY count DESC LIMIT 10;
And this query takes almost five seconds to complete, and that seems to me
quite slow. The database is running on a Duron 700 with 256 MB RAM and
ATA-66 disks. Operating system is FreeBSD-4.2.
Are there some tuning knobs I could turn?
regards,
le
--
Lukas Ertl eMail: l(dot)ertl(at)univie(dot)ac(dot)at
WWW-Redaktion Tel.: (+43 1) 4277-14073
Zentraler Informatikdienst (ZID) Fax.: (+43 1) 4277-9140
der Universität Wien
From | Date | Subject | |
---|---|---|---|
Next Message | David Olbersen | 2001-03-28 20:54:39 | Re: Query performance question |
Previous Message | Vijay Deval | 2001-03-28 14:33:34 | Re: Problems accessing TCL functions |