The following bug has been logged online:

Bug reference: 1325
Logged by: gregory

Email address: gperuch (AT) citer (DOT) fr

PostgreSQL version: 8.0 Beta

Operating system: win2000

Description: like error

Details:

I create a table


CREATE TABLE tbinvoicerows
(
invoicecd varchar(16) NOT NULL,
articlecd varchar(5),
articlenm varchar(32),
familycd varchar(1),
articlepriority int4,
quantity numeric(8,2),
unitprice numeric(8,2),
unity varchar(32),
vattypecd char(2),
vatratio numeric(9,3),
amountbt numeric(11,5),
currency varchar(20),
commratio numeric(9,3),
discountbt numeric(8,2),
discountratio numeric(9,3),
relratio numeric(5,2),
"timestamp" timestamp
)
WITHOUT OIDS;
ALTER TABLE tbinvoicerows OWNER TO sa;

after
I copy 5 millions lignes on the table

after
CREATE INDEX "idx_invoiceCd"
ON tbinvoicerows
USING btree
(invoicecd);


after

select * from tbinvoicerows
where invoicecd = 'FLOCAA00000025'

result in 0 seconde with 2 records NO BUG

select * from tbinvoicerows
where invoicecd like '%FLOCAA00000025%'

result in 40 secondes with 2 records NO BUG

select * from tbinvoicerows
where invoicecd like 'FLOCAA00000025%'

result in 40 secondes with 2 records BUG
Bug : the result is good but I expect a result faster as the first select
(like in mssql 7)

I test Postgres since One Week.... for the moment NICE WORK
Congratulation

Gregory










---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

PostgreSQL Bugs List wrote:
Almost certainly not a bug - you might be better subscribing to the
general/sql/performance mailing lists for this.

You want to:
1. VACUUM ANALYZE the table
2. Check your database was initialised with the "C" locale
3. Examine EXPLAIN ANALYZE to see how many rows the planner is expecting.

You can find plenty of discussion of all of these in the mailing list
archives. Also you might want to read the FAQ, particularly section 4.8
http://www.postgresql.org/docs/faqs/FAQ.html
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Thu, 18 Nov 2004, PostgreSQL Bugs List wrote:

To add to the reply already given...

If you're running in "C" locale, the last should be able to use a normal
index on invoicecd. However, since that didn't appear to happen, either
it's estimating a large number of rows which is unlikely or you are in
some other locale.

For other locales, you may want a pattern_ops index, which would be
something like:
create index foo on tbinvoicerows(invoicecd varchar_pattern_ops).

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly