Message-ID: <3C3C1132.E51307CF@tpf.co.jp>
Date: Wed, 09 Jan 2002 18:45:22 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
MIME-Version: 1.0
To: Daniel Kalchev <daniel@digsys.bg>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: again on index usage
References: <200201090745.JAA15141@dcave.digsys.bg>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit

Daniel Kalchev wrote:
> 
> >>>Tom Lane said:
>  > Daniel Kalchev <daniel@digsys.bg> writes:
>  > > Same result (sorry, should have included this originally):
>  >
>  > >>> If you say "set enable_seqscan to off", does that change the plan?
>  >
>  > > Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
>  > >   ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112
>  > > (cost=0.00..100217.52 rows=56873 width=8)
>  >
>  > So, what we've got here is a difference of opinion: the planner thinks
>  > that the seqscan will be faster.  How many rows are actually selected
>  > by this WHERE clause?  How long does each plan actually take?
>  >
>  >                      regards, tom lane
> 
> 3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns
> 4062 rows. Out of ca 1700000 rows.
> 
> With only the datetime constraints (which relates to the index), the number of
> rows is 51764.

The planner estimates 56873 rows. It seems not that bad.
A plausible reason is your table is nearly clustered 
as to ipdate.

regards,
Hiroshi Inoue