JOIN index/sequential select problem

From: gjerde(at)icebox(dot)org
To: pgsql-sql(at)postgresql(dot)org
Subject: JOIN index/sequential select problem
Date: 1999-05-12 21:09:21
Message-ID: Pine.LNX.4.05.9905121559230.30984-100000@snowman.icebox.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)
-> Index Scan 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 1999-05-12 22:56:18 Re: [SQL] JOIN index/sequential select problem
Previous Message Jan Wieck 1999-05-12 15:39:41 Re: [SQL] question about money type