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 DFBEB54D07 for ; Sun, 13 Mar 2005 06:08:01 +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 27725-05 for ; Sun, 13 Mar 2005 06:07:52 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 7AB5054507 for ; Sun, 13 Mar 2005 06:07:50 +0000 (GMT) Received: (qmail 13243 invoked from network); 13 Mar 2005 06:07:47 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.54.70) by mail.pws.com.au with SMTP; 13 Mar 2005 06:07:47 -0000 From: Russell Smith To: "Tom Pfeifer" Subject: Re: Index use and slow queries Date: Sun, 13 Mar 2005 17:07:46 +1100 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org References: <20050313051830.M98712@tela.com> In-Reply-To: <20050313051830.M98712@tela.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200503131707.46690.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.03 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/214 X-Sequence-Number: 10919 On Sun, 13 Mar 2005 04:40 pm, Tom Pfeifer wrote: > Hello, >=20 >=20 > My version of Postgresql is=A07.4.3.=20 > I have a simple table with 2 indexes:=20 > =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"=20 > =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=20 > --------+-----------------------------+----------------------------------= =2D--=20 > =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)=20 > =A0mmd5=A0=A0 | character varying(32)=A0=A0=A0=A0=A0=A0 | not null=20 > =A0active | character(1)=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | n= ot null=20 > =A0lud=A0=A0=A0 | timestamp without time zone | default now()=20 > Indexes:=20 > =A0=A0=A0 "tst_idx" unique, btree (mmd5, active)=20 > =A0=A0=A0 "tst_tst_id_key" unique, btree (tst_id)=20 > =20 >=20 >=20 > There are exactly 1,000,000 (one million) rows in the table (tst).=A0 The= re are no NULLS, empty columns in any row. >=20 >=20 > I get really fast response times=A0when=A0using the following select stat= ement (Less than 1 second).=20 > maach=3D# explain select * from tst where mmd5 =3D '71e1c18cbc708a0bf28fe= 106e03256c7' and active =3D 'A';=20 > =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 PLAN= =20 > -------------------------------------------------------------------------= =2D----------------------------=20 > =A0Index Scan using tst_idx on tst=A0 (cost=3D0.00..6.02 rows=3D1 width= =3D57)=20 > =A0=A0 Index Cond: (((mmd5)::text =3D '71e1c18cbc708a0bf28fe106e03256c7':= :text) AND (active =3D 'A'::bpchar))=20 > (2 rows)=20 > =20 >=20 >=20 > I get=A0really slow repoonse times=A0when=A0using the following select st= atement (About 20 seconds).=20 > maach=3D# explain select * from tst where tst_id =3D 639246;=20 Before 8.0, bigint would not use an index unless you cast it, or quote it. eg explain select * from tst where tst_id =3D 639246::int8;=20 explain select * from tst where tst_id =3D '639246';=20 Hope this helps. Russell Smith