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
|
| Список | 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 по дате отправления: