Re: Query performance question

URL is a very large field.  If an extra field is created which gives a
neumeric id to the url, run the query on the number, and then get the desired
output

Vijay

Lukas Ertl wrote:

> 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;
>
>