Another index "buglet"? - Mailing list pgsql-hackers

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.95rows=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@hub.org           secondary: scrappy@{freebsd|postgresql}.org