Re: Index usage on date feild , differences in '>' and '>=' - Mailing list pgsql-sql

On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote:

> I am trying to improve a  query to use  existing indexes but facing diffculty.
>
>
> Looks like 'between' amd '>=' are not using indexes althoug > and < does.
> all my application code uses between and i am sure it use to work fine
> at one point of time.
>
>
> regds
> mallah.
>
> SQL TRANSCRIPT:
> ======================================================================================
>
> tradein_clients=> explain  select   list_id from eyp_rfi a where  generated >  '2002-08-13' and generated <
'2002-09-19'  ;
 
> NOTICE:  QUERY PLAN:
>
> Index Scan using eyp_rfi_date on eyp_rfi a  (cost=0.00..15796.97 rows=4150 width=4)
>
> EXPLAIN
> tradein_clients=> explain  select   list_id from eyp_rfi a where  generated >=  '2002-08-13' and generated <
'2002-09-19'  ;
 
> NOTICE:  QUERY PLAN:
>
> Seq Scan on eyp_rfi a  (cost=0.00..17923.81 rows=5061 width=4)

Given how close the two results are (and the fact that they differ by 900
rows), have you tried using set enable_seqscan=off and seeing what
explain gives you for the second query?  My guess is that it'll have
an estimated cost greater than the 17923.81 it's estimating from the
sequence scan.