Thread: Index usage on date feild , differences in '>' and '>=' and between
Index usage on date feild , differences in '>' and '>=' and between
From
"Rajesh Kumar Mallah."
Date:
Hi , 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) 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) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-08-13' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> ====================================================================================== -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
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.
Re: Index usage on date feild , differences in '>' and '>=' and between
From
"Rajesh Kumar Mallah."
Date:
Thanks very much for the response. set enable_seqscan=off; Definitely helps. and for wide date ranges it usees indexes. But with default value of enable_sequence changing date range seems to have effect. can you explain me a bit more or point me to right documents for understanding the languae of "EXPLAIN". ==================================================================== EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) EXPLAIN tradein_clients=> ====================================================================== the distribution of values are as follows: > select generated ,count(generated) from eyp_rfi a where generated between '2002-09-10' and '2002-09-19' group bygenerated; generated | count ------------+-------2002-09-10 | 4422002-09-11 | 10602002-09-12 | 6412002-09-13 | 6072002-09-14 | 13202002-09-15| 5212002-09-16 | 14742002-09-17 | 9402002-09-18 | 10052002-09-19 | 178 (10 rows) Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI Thanks and Regards Rajesh Mallah. Tsday 19 September 2002 12:07, Stephan Szabo wrote: > 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. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: > Thanks very much for the response. > set enable_seqscan=off; Definitely helps. > and for wide date ranges it usees indexes. > > > But with default value of enable_sequence changing date range seems to have effect. > can you explain me a bit more or point me to right documents for understanding > the languae of "EXPLAIN". > ==================================================================== > EXPLAIN > tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ; > NOTICE: QUERY PLAN: > > Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) > > EXPLAIN > tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ; > NOTICE: QUERY PLAN: > > Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) > > EXPLAIN Note the cost and row estimates for the two queries and for the first query with and without enable_seqscan=off; My guess is that if you do an explain with it off, you'll get a cost number >17923.81 which is why it's picking the seq scan, becaust it's guessing that it'll be faster. The row count seems off by a factor of 2 from the numbers below, have you analyzed recently and how many rows are in the table as a whole? Also, I'm guessing that your data is probably well ordered in relation to those dates as opposed to truly random which may throw off the estimates as well, what version are you using? set enable_seqscan=off is kinda dangerous since sometimes the seq scan is actually faster. > the distribution of values are as follows: > > select generated ,count(generated) from eyp_rfi a where generated between '2002-09-10' and '2002-09-19' group bygenerated; > > > generated | count > ------------+------- > 2002-09-10 | 442 > 2002-09-11 | 1060 > 2002-09-12 | 641 > 2002-09-13 | 607 > 2002-09-14 | 1320 > 2002-09-15 | 521 > 2002-09-16 | 1474 > 2002-09-17 | 940 > 2002-09-18 | 1005 > 2002-09-19 | 178 > (10 rows) > > Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI