Hello,

My version of Postgresql is 7.4.3.
I have a simple table with 2 indexes:
                             Table "public.tst"
 Column |            Type             |              Modifiers
--------+-----------------------------+-------------------------------------
 tst_id | bigint                      | default nextval('tst_id_seq'::text)
 mmd5   | character varying(32)       | not null
 active | character(1)                | not null
 lud    | timestamp without time zone | default now()
Indexes:
    "tst_idx" unique, btree (mmd5, active)
    "tst_tst_id_key" unique, btree (tst_id)

There are exactly 1,000,000 (one million) rows in the table (tst).  There are no NULLS, empty columns in any row.

I get really fast response times when using the following select statement (Less than 1 second).
maach=# explain select * from tst where mmd5 = '71e1c18cbc708a0bf28fe106e03256c7' and active = 'A';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Index Scan using tst_idx on tst  (cost=0.00..6.02 rows=1 width=57)
   Index Cond: (((mmd5)::text = '71e1c18cbc708a0bf28fe106e03256c7'::text) AND (active = 'A'::bpchar))
(2 rows)

I get really slow repoonse times when using the following select statement (About 20 seconds).
maach=# explain select * from tst where tst_id = 639246;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on tst  (cost=0.00..23370.00 rows=1 width=57)
   Filter: (tst_id = 639246)
(2 rows)

Why is the second select statement so slow, it should be using the "tst_tst_id_key" unique, btree (tst_id) index, but instead EXPLAIN says it's using a Seq Scan.  If it was using the index, this select statement should be as fast if not faster than the above select statement.

When I turned off,  maach=# SET ENABLE_SEQSCAN TO OFF;
The slow select statement gets even slower.
maach=# explain select * from tst where tst_id = 639246;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on tst  (cost=100000000.00..100023370.00 rows=1 width=57)
   Filter: (tst_id = 639246)
(2 rows)

Why do I have to use 2 columns to create a fast/efficient index?  I want to get the single column index to be the fastest index for my select statements.  How do I accomplish this.

Thanks,
Tom

Reply via email to