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
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 |