function based index problem

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

Responses

Browse pgsql-admin by date

  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

Browse pgsql-sql by date

  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?