Re: [SQL] [ADMIN] function based index problem

2011-08-31 Thread Kevin Grittner
Viktor Bojovi* wrote: > i don't know why it doesn't use index scan. Because it thinks your query will return 81226 rows. We need more information to make many suggestions beyond "make sure the table has been analyzed". http://wiki.postgresql.org/wiki/SlowQueryQuestions Also, it's not consi

[SQL] Want some basic compare of data type on PostgreSQL and MySQL

2011-08-31 Thread bhavesh1385
Hello All, I Want some basic compare of data type on PostgreSQL and MySQL. I am Try to using create the database on PostgreSQL with the same query on MySQL then it will create a problem... I have make changes according to the Datatype of PostgreSQL and below are the syntax for the query. Into M

Re: [SQL] function based index problem

2011-08-31 Thread Viktor Bojović
On Thu, Sep 1, 2011 at 12:09 AM, David Johnston wrote: > ** ** > > *From:* pgsql-sql-ow...@postgresql.org [mailto: > pgsql-sql-ow...@postgresql.org] *On Behalf Of *Viktor Bojovic > *Sent:* Wednesday, August 31, 2011 5:27 PM > *To:* pgsql-sql@postgresql.org; pgsql-ad...@postgresql.org > *Subject:*

Re: [SQL] function based index problem

2011-08-31 Thread David Johnston
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Viktor Bojovic Sent: Wednesday, August 31, 2011 5:27 PM To: pgsql-sql@postgresql.org; pgsql-ad...@postgresql.org Subject: [SQL] function based index problem Hi, on table entry (17M records) there is on

[SQL] function based index problem

2011-08-31 Thread Viktor Bojović
Hi, on table entry (17M records) there is one index: CREATE INDEX ndxlen ON uniprot_frekvencije.entry USING btree (length(sequence::text)); When using ">=" in search which returns only two records, query runs much (hundred times) slower. i don't know why it doesn't use index scan. I just wa

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-31 Thread Pavel Stehule
2011/8/31 Emi Lu : > On 08/31/2011 03:16 AM, Emre Hasegeli wrote: >> >> 2011/8/30 Emi Lu: >> >>> First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will >>> work for me. >>> >>> But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it? >> >> It is not. It is like "where

[SQL] ERROR: cannot subscript type text because it is not an array

2011-08-31 Thread lxnf98mm
I have table with field l type lseg Column | Type | Modifiers +-+--- cnt| integer | used | boolean | l | lseg| c | text| select l from t where used = false group by l[0]; ERROR: cannot subscript type text because it is not an array select

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-31 Thread Emi Lu
On 08/31/2011 03:16 AM, Emre Hasegeli wrote: 2011/8/30 Emi Lu: First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will work for me. But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it? It is not. It is like "where id = (3, 5, 7)". What I mean is ilike ('%

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-31 Thread Jasen Betts
On 2011-08-30, Emi Lu wrote: > Hi Tom, > select * from tablename where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])); >> >>> If next version could have "not ilike ('', '')" added into window >>> functions, that's will be great! >> >> Why? And what's this got to do with window f

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-31 Thread Emre Hasegeli
2011/8/30 Emi Lu : > First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will > work for me. > > But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it? It is not. It is like "where id = (3, 5, 7)". -- Emre Hasegeli Veri Tabanı Yöneticisi Tart İnternet Teknolojiler