From: | "Thomas A(dot) Lowery" <tlowery(at)stlowery(dot)net> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Performance Problem Index Ignored, but why |
Date: | 2002-05-22 04:02:05 |
Message-ID: | 20020522000205.A17321@stllnx1.stlassoc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-05-22 04:26:35 | Re: Performance Problem Index Ignored, but why |
Previous Message | Christopher Smith | 2002-05-22 03:25:01 | port database to support japanese characters |