JOIN index/sequential select problem

Поиск
Список
Период
Сортировка
От gjerde@icebox.org
Тема JOIN index/sequential select problem
Дата
Msg-id Pine.LNX.4.05.9905121559230.30984-100000@snowman.icebox.org
обсуждение исходный текст
Ответ на Re: [SQL] plpgsql error  (jwieck@debis.com (Jan Wieck))
Ответы Re: [SQL] JOIN index/sequential select problem  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
Hi,
I'm having a minor problem with one of my queries.

inventorysuppliers.id
av_parts.rawpartnumber
av_parts.vendorid
all have indexes.

Why in the world is postgres selecting seq scan on the inventorysuppliers
table when doing an LIKE?  That doesn't make sense to me.

PG version: 6.5 CVS as of monday.
OS: Redhat 6.0, Linux 2.2.6, glibc 2.1
Platform: i386

Running query with explain:
Nested Loop  (cost=440.35 rows=1 width=100) ->  Seq Scan on inventorysuppliers  (cost=11.90 rows=209 width=40) ->
IndexScan using av_parts_vendorid_index on av_parts  (cost=2.05
 
rows=1 width=60)

Running query with last line as:           AND (AV_Parts.RawPartNumber = '6890040')

Nested Loop  (cost=4.10 rows=1 width=100) ->  Index Scan using av_parts_rawpartnumber_index on av_parts
(cost=2.05 rows=1 width=60) ->  Index Scan using inventorysuppliers_id_index on inventorysuppliers
(cost=2.05 rows=209 width=40)

Query:
SELECT  AV_Parts.PartNumber,       AV_Parts.Description,       AV_Parts.NSN,       AV_Parts.Quantity,
AV_Parts.Condition,      inventorysuppliers.companyname,       inventorysuppliers.phone,       inventorysuppliers.fax,
    AV_Parts.ItemID,       AV_Parts.VendorID   FROM       AV_Parts, inventorysuppliers               WHERE
(AV_Parts.VendorID= inventorysuppliers.id)             AND (AV_Parts.RawPartNumber LIKE '6890040%')
 

Thanks,
Ole Gjerde



В списке pgsql-sql по дате отправления:

Предыдущее
От: jwieck@debis.com (Jan Wieck)
Дата:
Сообщение: Re: [SQL] question about money type
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [SQL] JOIN index/sequential select problem