Re: [PERFORM] Performance regarding LIKE searches

2010-03-30 Thread Matthew Wakeling
On Mon, 29 Mar 2010, randa...@bioinfo.wsu.edu wrote: WHERE ... lower(n.name) LIKE 'Scaffold:scaffold_163:1000..1199%' ... I'm sure you noticed that this is never going to return any rows? Matthew -- Me... a skeptic? I trust you have proof? -- Sent via pgsql-performance mailing list (pgsql-p

Re: [PERFORM] Performance regarding LIKE searches

2010-03-29 Thread Andy Colson
- Original Message - From: "Tom Lane" To: randa...@bioinfo.wsu.edu Cc: pgsql-performance@postgresql.org Sent: Monday, March 29, 2010 10:00:03 AM Subject: Re: [PERFORM] Performance regarding LIKE searches randa...@bioinfo.wsu.edu writes: I can see I am hitting an index using an in

Re: [PERFORM] Performance regarding LIKE searches

2010-03-29 Thread randalls
To: randa...@bioinfo.wsu.edu Cc: pgsql-performance@postgresql.org Sent: Monday, March 29, 2010 10:00:03 AM Subject: Re: [PERFORM] Performance regarding LIKE searches randa...@bioinfo.wsu.edu writes: > I can see I am hitting an index using an index that I created using the > varchar_pattern_op

Re: [PERFORM] Performance regarding LIKE searches

2010-03-29 Thread Tom Lane
randa...@bioinfo.wsu.edu writes: > I can see I am hitting an index using an index that I created using the > varchar_pattern_ops setting. This is very fast and performs like I would > expect. However, when my application, GBrowse, access the database, I see in > my slow query log this: > 2010

[PERFORM] Performance regarding LIKE searches

2010-03-29 Thread randalls
Hi, I am querying a Postgresql 8.3 database table that has approximately 22 million records. The (explain analyze) query is listed below: gdr_gbrowse_live=> explain analyze SELECT f.id,f.object,f.typeid,f.seqid,f.start,f.end,f.strand FROM feature as f, name as n WHERE (n.id=f.id AND lower(n.n