From tpfeifer@tela.com Sun Mar 13 05:40:49 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 53DB7564B4
for Hello, My version of Postgresql is=A07.4.3.
There are exactly 1,000,000 (one million) rows in the t=
able (tst).=A0 There are no NULLS, empty columns in any row. I get really fast response times=A0when=A0using the fol=
lowing select statement (Less than 1 second).
I get=A0really slow repoonse times=A0when=A0using the f=
ollowing select statement (About 20 seconds).
Why is the second select statement so slow, it should b=
e using the "tst_tst_id_key" unique, btree (tst_id) index, but in=
stead EXPLAIN says it's using a Seq Scan.=A0 If it was using the index, thi=
s select statement should be as fast if not faster than the above select st=
atement. When I turned off,=A0 maach=3D# SET ENABLE_SEQSCAN TO O=
FF;
Why do I have to use 2 columns=A0to create a fast/effic=
ient index?=A0=A0I want to get the single column index to be the fastest in=
dex for my select statements.=A0 How do I accomplish this. Thanks,
I have a simple table with 2 indexes:
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 Table "public.tst"
=A0Column |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Modifiers
--------+-----------------------------+------------------------------=
-------
=A0tst_id | bigint=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 | default nextval('tst_id_seq'::text)
=A0mmd5=A0=A0 | character varying(32)=A0=A0=A0=A0=A0=A0 | not null
=A0active | character(1)=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
| not null
=A0lud=A0=A0=A0 | timestamp without time zone | default now()
Indexes:
=A0=A0=A0 "tst_idx" unique, btree (mmd5, active)
=A0=A0=A0 "tst_tst_id_key" unique, btree (tst_id)
maach=3D# explain select * from tst where mmd=
5 =3D '71e1c18cbc708a0bf28fe106e03256c7' and active =3D 'A';
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PL=
AN
---------------------------------------------------------------------=
---------------------------------
=A0Index Scan using tst_idx on tst=A0 (cost=3D0.00..6.02 rows=3D1 wid=
th=3D57)
=A0=A0 Index Cond: (((mmd5)::text =3D '71e1c18cbc708a0bf28fe106e03256=
c7'::text) AND (active =3D 'A'::bpchar))
(2 rows)
maach=3D# explain select * from tst where tst_id =3D 639246;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QU=
ERY PLAN
--------------------------------------------------------
=A0Seq Scan on tst=A0 (cost=3D0.00..23370.00 rows=3D1 width=3D57)
=A0=A0 Filter: (tst_id =3D 639246)
(2 rows)
The slow select statement gets even slower.
maach=3D# explain select * from tst where tst_id =3D 639246;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 QUERY PLAN
--------------------------------------------------------------------
=A0Seq Scan on tst=A0 (cost=3D100000000.00..100023370.00 rows=3D1 wid=
th=3D57)
=A0=A0 Filter: (tst_id =3D 639246)
(2 rows)
Tom