Re: Performance Problem Index Ignored, but why

> estimate that 139654 rows will match f_state = 'PA' in the right
No, 375342 is the actual number. Using the index does appear slower
(limited testing noted).

explain select count(*) from state_tst where f_state = 'PA'/
NOTICE: QUERY PLAN:

Aggregate (cost=277899.65..277899.65 rows=1 width=0)
-> Seq Scan on state_tst (cost=0.00..277550.51 rows=139654
width=0)

select count(*) from state_tst where f_state = 'PA'/
count
'375342'

Elapsed: 139 wallclock secs

set enable_seqscan = off/

explain select count(*) from state_tst where f_state = 'PA'/
NOTICE: QUERY PLAN:

Aggregate (cost=542303.53..542303.53 rows=1 width=0)
-> Index Scan using st_f_state_idx on state_tst
(cost=0.00..541954.39 rows=139654 width=0)

select count(*) from state_tst where f_state = 'PA'/
count
'375342'

Elapsed: 222 wallclock secs

Tom

On Wed, May 22, 2002 at 12:26:35AM -0400, Tom Lane wrote:
> "Thomas A. Lowery" <tlowery(at)stlowery(dot)net> writes:
> > Can I force the use of an index?
>
> Try "set enable_seqscan = off". But on the basis of what you've shown,
> it's not obvious that an indexscan will be faster. Is the planner's
> estimate that 139654 rows will match f_state = 'PA' in the right
> ballpark?
>
> regards, tom lane