Thread: Query speed
I'm working on moving several medium size tables from Oracle to Postgres. The table I'm dealing with has 210000 rows. I've indexed the table with a simple single column index on a varchar(30) column that is unique but doesn't have a unique constraint placed on it.
When I issue a select* from table where column = part,
query speed is great, however, changing the where clause to "where column like 'part%'" the explain analyze shows the database doing a full table scan and not utilzing the index which slows the query speed.
Oracles optimizer, on the other hand, will use the index so that there is no comparison in terms of query speed...
How can I get Postgresql's optimizer to use the index?
When I issue a select* from table where column = part,
query speed is great, however, changing the where clause to "where column like 'part%'" the explain analyze shows the database doing a full table scan and not utilzing the index which slows the query speed.
Oracles optimizer, on the other hand, will use the index so that there is no comparison in terms of query speed...
How can I get Postgresql's optimizer to use the index?
RFLM101@aol.com writes: > When I issue a select* from table where column = part, > query speed is great, however, changing the where clause to "where column > like 'part%'" the explain analyze shows the database doing a full table scan > and not utilzing the index which slows the query speed. Unless you're in the "C" locale, LIKE queries currently won't use an index. This is apparently not easy to fix--if you search the list archives you'll find some discussions on it. -Doug