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 ; Sun, 13 Mar 2005 05:40:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22440-04 for ; Sun, 13 Mar 2005 05:40:48 +0000 (GMT) Received: from spider.tela.com (spider.tela.com [206.98.7.34]) by svr1.postgresql.org (Postfix) with ESMTP id BA64654E1E for ; Sun, 13 Mar 2005 05:40:47 +0000 (GMT) Received: from tela.com (localhost [127.0.0.1]) by spider.tela.com (Postfix) with ESMTP id 63705111DEE4 for ; Sat, 12 Mar 2005 23:40:47 -0600 (CST) From: "Tom Pfeifer" To: pgsql-performance@postgresql.org Subject: Index use and slow queries Date: Sat, 12 Mar 2005 23:40:47 -0600 Message-Id: <20050313051830.M98712@tela.com> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 206.103.217.120 (tpfeifer) MIME-Version: 1.0 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.187 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: * X-Archive-Number: 200503/213 X-Sequence-Number: 10918

Hello,

My version of Postgresql is=A07.4.3.
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)

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).
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)

I get=A0really slow repoonse times=A0when=A0using the f= ollowing select statement (About 20 seconds).
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)

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;
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)

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,
Tom