Another index "buglet"?
От | The Hermit Hacker |
---|---|
Тема | Another index "buglet"? |
Дата | |
Msg-id | Pine.BSF.4.21.0001080311300.18498-100000@thelab.hub.org обсуждение исходный текст |
Ответы |
Re: [HACKERS] Another index "buglet"?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-hackers по дате отправления: