function based index problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux