Performance Problem Index Ignored, but why
От | Thomas A. Lowery |
---|---|
Тема | Performance Problem Index Ignored, but why |
Дата | |
Msg-id | 20020521220113.A17282@stllnx1.stlassoc.com обсуждение исходный текст |
Ответы |
Re: Performance Problem Index Ignored, but why
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: