| 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: | Whole Thread | Raw Message | 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? |