sql - PostgreSQL text range scan -

-

i have written query aim 10 results including current one, padding 9 entries on either side alphabetical list can sorted reciever. query using, issue not result, but because neither of queries using index.

(   select     uid,     title       books     lower(title) < lower('frankenstein')   order title desc   limit 9 ) union (   select     uid,     title       books     lower(title) >= lower('frankenstein')   order title   limit 10 ) order title;

the index trying utilize simple btree, no text_pattern_ops etc below:

create index books_title_idx on books using btree (lower(title));

if run explain on first part of unioin, in spite of limit , order, performs full table scan

explain analyze  select   uid,   title   books lower(title) < lower('frankenstein') order title desc limit 9

limit (cost=69.04..69.06 rows=9 width=152) (actual time=6.276..6.292 rows=9 loops=1) -> sort (cost=69.04..69.67 rows=251 width=152) (actual time=6.273..6.277 rows=9 loops=1) sort key: ((title)) sort method: top-n heapsort memory: 25kb -> seq scan on books (cost=0.00..63.80 rows=251 width=152) (actual time=0.056..5.227 rows=267 loops=1) filter: (lower((title)) < 'frankenstein'::text) rows removed filter: 486 total runtime: 6.359 ms

when equality check on same query - the index used

explain analyze select   uid,   title   books lower(title) = lower('frankenstein') order title desc

sort (cost=17.04..17.05 rows=4 width=152) (actual time=0.054..0.054 rows=0 loops=1) sort key: ((title)) sort method: quicksort memory: 25kb -> bitmap heap scan on books (cost=4.31..17.00 rows=4 width=152) (actual time=0.041..0.041 rows=0 loops=1) recheck cond: (lower((title)) = 'frankenstein'::text) -> bitmap index scan on books_title_idx (cost=0.00..4.31 rows=4 width=0) (actual time=0.036..0.036 rows=0 loops=1) index cond: (lower((title)) = 'frankenstein'::text) total runtime: 0.129 ms

and same applies when between query

explain analyze select   uid,   title   books lower(title) > lower('frankenstein') , lower(title) < lower('gulliver''s travels') order title

sort (cost=17.08..17.09 rows=4 width=152) (actual time=0.511..0.529 rows=25 loops=1) sort key: (title) sort method: quicksort memory: 27kb -> bitmap heap scan on books (cost=4.33..17.04 rows=4 width=152) (actual time=0.118..0.213 rows=25 loops=1) recheck cond: ((lower(title) > 'frankenstein'::text) , (lower(title) < 'gulliver''s travels'::text)) -> bitmap index scan on books_title_idx (cost=0.00..4.33 rows=4 width=0) (actual time=0.087..0.087 rows=25 loops=1) index cond: ((lower(title) > 'frankenstein'::text) , (lower(title) < 'gulliver''s travels'::text)) total runtime: 0.621 ms

what looking here not between search because beginning , end unknown. postgresql limitation or there other manipulating cost of table scan silly can use convince query planner use index?

i using postgresql 9.3

use:

order lower(title) desc

or

order lower(title)

to match functional index, can utilized.
order by irrelevant selection of rows in other 2 queries. that's why index can used in cases.


Comments

Post a Comment

Popular posts from this blog

c# - Unity IoC Lifetime per HttpRequest for UserStore -

-
i'm trying clean default implementation of accountcontroller.cs comes out of box in new mvc5/owin security implementation. have modified constructor this: private usermanager<applicationuser> usermanager; public accountcontroller(usermanager<applicationuser> usermanager) { this.usermanager = usermanager; } also, have created lifetime manager unity looks this: public class httpcontextlifetimemanager<t> : lifetimemanager, idisposable { private httpcontextbase _context = null; public httpcontextlifetimemanager() { _context = new httpcontextwrapper(httpcontext.current); } public httpcontextlifetimemanager(httpcontextbase context) { if (context == null) throw new argumentnullexception("context"); _context = context; } public void dispose() { this.removevalue(); } public override object get...
Read more

Change the color of an oval at click in Java AWT -

-
i have draw ovals in java, , @ click change color. beginning tried change color after 20 ms, doesn't work. my code is: public class mycomponentnew extends frame { public graphics2d g2d; public mycomponentnew(string title) { super(title); setsize(400, 550); } @override public void paint(graphics g) { this.g2d = (graphics2d) g; this.g2d.setcolor(color.red); this.g2d.filloval(10, 55, 50, 100); } public void changecolor () { this.g2d.setcolor(color.blue); this.g2d.filloval(10, 55, 50, 100); } } and in class main method have: mycomponentnew m; m = new mycomponentnew("fereastra cu baloane"); m.setvisible(true); m.addwindowlistener(new windowadapter() { @override public void windowclosing(windowevent we) { system.exit(0); } }); try { thread.sleep(20); } catch(interruptedexception e) {} m.changecolor(); the color of oval remains red. ...
Read more

I am trying to solve the error message 'incompatible ranks 0 and 1 in assignment' in a fortran 95 program. -

-
i writing program in fortran find velocity of parachuting person in relation time. keep getting error can't fix. new programming , appreciated. the error is v(i+1)=v(i)+[32-((c*v(i)*v(i))/m)]*(h) 1 error: incompatible ranks 0 , 1 in assignment @ (1) and program is program para integer :: real :: v(11) !velocity real :: q !initial velocity real :: h !time step real :: c !drag coefficient real :: m !mass ! gravity equal 32 ft/s^2 write (*,*)'enter time step' read(*,*)h write(*,*)'enter initial velocity' read(*,*)q write(*,*)'enter drag coefficient' read(*,*)c write(*,*)'enter mass' read(*,*)m i=1,10 ! 1 10, 1 being interval. end v(i+1)=v(i)+[32-((c*v(i)*v(i))/m)]*(h) q=v(1) end program you cannot use [] normal parenthesis in expressions. array constructor, [ items ] means array items elements. end do should after line.
Read more