From: | Viktor Bojović <viktor(dot)bojovic(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org |
Subject: | function based index problem |
Date: | 2011-08-31 21:27:29 |
Message-ID: | CAJu1cLZvOYadLj3g60Q6Tnqtjk1CZPjdr=T4E89MzUeWaR8TRg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
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
wanted to ask how can i modify the query to use that index? Explain plans
are pasted below.
bioinf=> explain select * from entry where length(sequence)=36805;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on entry (cost=1523.54..294886.26 rows=81226 width=1382)
Recheck Cond: (length((sequence)::text) = 36805)
-> Bitmap Index Scan on ndxlen (cost=0.00..1503.23 rows=81226 width=0)
Index Cond: (length((sequence)::text) = 36805)
(4 rows)
bioinf=> explain select * from entry where length(sequence)>=36805;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on entry (cost=0.00..5400995.21 rows=5415049 width=1382)
Filter: (length((sequence)::text) >= 36805)
(2 rows)
Thanx in advance
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2011-08-31 21:30:14 | Re: Segmentation fault |
Previous Message | Campbell, Lance | 2011-08-31 21:23:32 | Re: Segmentation fault |
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2011-08-31 21:53:52 | Re: function based index problem |
Previous Message | Pavel Stehule | 2011-08-31 16:16:02 | Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? |