From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index usage on date feild , differences in '>' and '>='
Date: Thu, 19 Sep 2002 07:28:01 -0700 (PDT)
Message-ID: <20020919072108.S27151-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200209191236.11671.mallah@trade-india.com>
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 by generated;
>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, mallah@trade-india.com
Subject: Re: Index usage on date feild , differences in '>' and '>='
In-Reply-To: <20020919072108.S27151-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox