Обсуждение: Performance Problem Index Ignored, but why

I've the task of porting a current Oracle application to PostgreSQL.

Database: 7.2.1
OS: Linux 2.4.9-13smp

I've an odd thing happening with a query.  Using a simple table:

            Table "state_tst"
Column  |         Type         | Modifiers
---------+----------------------+-----------
id      | integer              | not null
v_state | character varying(2) |
f_state | character(2)         |
Indexes: st_f_state_idx,
    st_v_state_idx
Primary key: state_tst_pkey

id is a sequence number and primary key, v_state and f_state are 2
character U.S. States.  I created v_state as varchar(2) and f_state as
char(2) to test if the query explained/performed differently (it
doesn't).

CREATE INDEX st_v_state_idx ON state_tst USING btree (v_state);
CREATE INDEX st_f_state_idx ON state_tst USING btree (f_state);

Load the table using a copy from ...

vacuum verbose analyze state_tst;

Total rows: 14309241

Queries using either f_state = or v_state =  explain (and appear to
execute) using a sequential scan.  Resulting in 60 - 80 second query
times.

Can I force the use of an index?  Or do I have something wrong?  Any
ideas?

pg_test=# 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)

EXPLAIN

"Thomas A. Lowery" <tlowery@stlowery.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

> 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@stlowery.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