Another index "buglet"?

Query is:

SELECT url.status,url2.url,url.url
FROM url,url url2
WHERE url.referrer=url2.rec_id;

There is an index on rec_id and one on referrer ... shouldn't one of the
be used? Like, I can see it having to go through every url2.rec_id, but
shouldn't the url.referrer= be abe to make use of an index? I thought
about changing the above to something like:

explain SELECT url.status,url2.url,url.url
FROM url,url url2
WHERE url.referrer IN ( SELECT rec_id FROM url );

but that didn't win me anything else :)

======

udmsearch=> create index url_rec_id on url using btree ( rec_id );
CREATE
udmsearch=> create index url_referrer on url using btree ( referrer );
CREATE
udmsearch=> explain SELECT url.status,url2.url,url.url FROM url,url url2 WHERE
udmsearch-> url.referrer=url2.rec_id;
NOTICE: QUERY PLAN:

Hash Join (cost=2045.81 rows=4544 width=36)
-> Seq Scan on url (cost=863.95 rows=4544 width=20)
-> Hash (cost=863.95 rows=4544 width=16)
-> Seq Scan on url url2 (cost=863.95 rows=4544 width=16)

EXPLAIN
udmsearch=> \d url
Table = url
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| rec_id | int4 not null default nextval ( | 4 |
| status | int4 not null default 0 | 4 |
| url | varchar() not null | 128 |
| content_type | varchar() not null default '' | 32 |
| last_modified | varchar() not null default '' | 32 |
| title | varchar() not null default '' | 128 |
| txt | varchar() not null default '' | 255 |
| docsize | int4 not null default 0 | 4 |
| last_index_time | int4 not null | 4 |
| next_index_time | int4 not null | 4 |
| referrer | int4 not null default 0 | 4 |
| tag | int4 not null default 0 | 4 |
| hops | int4 not null default 0 | 4 |
| keywords | varchar() not null default '' | 255 |
| description | varchar() not null default '' | 100 |
| crc | varchar() not null default '' | 33 |
+----------------------------------+----------------------------------+-------+
Indices: url_crc
url_pkey
url_rec_id
url_referrer
url_url

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org