Re: Index usage on date feild , differences in '>' and '>='

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.