Thread

1. BUG #1325: like error

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-18T10:14:21Z

   The following bug has been logged online:
   
   Bug reference:      1325
   Logged by:          gregory
   
   Email address:      gperuch@citer.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
   
   
   
   
   
   
   
   
   
   
   

2. Re: BUG #1325: like error

   Richard Huxton <dev@archonet.com> — 2004-11-18T11:56:50Z

   PostgreSQL Bugs List wrote:
   > The following bug has been logged online:
   > 
   > Bug reference:      1325
   > Logged by:          gregory
   > Email address:      gperuch@citer.fr
   > PostgreSQL version: 8.0 Beta
   > Operating system:   win2000
   > Description:        like error
   
   > after
   > I copy 5 millions lignes on the table
   
   > 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) 
   
   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
   
   

3. Re: BUG #1325: like error

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-11-18T14:18:44Z

   On Thu, 18 Nov 2004, PostgreSQL Bugs List wrote:
   
   > 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)
   
   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).