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

From mr-russ@pws.com.au Sun Mar 13 06:08:01 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 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 From tgl@sss.pgh.pa.us Sun Mar 13 06:26:22 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 535F654DA7 for ; Sun, 13 Mar 2005 06:26:22 +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 32231-06 for ; Sun, 13 Mar 2005 06:26:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8081754E05 for ; Sun, 13 Mar 2005 06:26:11 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2D6QCLg014750; Sun, 13 Mar 2005 01:26:12 -0500 (EST) To: Russell Smith Cc: "Tom Pfeifer" , pgsql-performance@postgresql.org Subject: Re: Index use and slow queries In-reply-to: <200503131707.46690.mr-russ@pws.com.au> References: <20050313051830.M98712@tela.com> <200503131707.46690.mr-russ@pws.com.au> Comments: In-reply-to Russell Smith message dated "Sun, 13 Mar 2005 17:07:46 +1100" Date: Sun, 13 Mar 2005 01:26:12 -0500 Message-ID: <14749.1110695172@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/215 X-Sequence-Number: 10920 Russell Smith writes: > On Sun, 13 Mar 2005 04:40 pm, Tom Pfeifer wrote: >> I get really slow repoonse times when using the following select statement (About 20 seconds). >> maach=# explain select * from tst where tst_id = 639246; > Before 8.0, bigint would not use an index unless you cast it, or quote it. > explain select * from tst where tst_id = 639246::int8; > explain select * from tst where tst_id = '639246'; ... or you compare to a value large enough to be int8 naturally, eg > explain select * from tst where tst_id = 123456639246; The issue here is that (a) 639246 is naturally typed as int4, and (b) before 8.0 we couldn't use cross-type comparisons such as int8 = int4 with an index. You can find a whole lot of angst about this issue and related ones if you care to review the last six or eight years of the pgsql-hackers archives. It was only recently that we found a way to support cross-type index operations without breaking the fundamental type-extensibility features of Postgres. (In hindsight, we spent way too much time fixated on the notion that we needed to find a way to implicitly convert the non-indexed value to match the indexed column's type, rather than biting the bullet and supporting cross-type operations directly with indexes. Oh well, hindsight is always 20/20.) regards, tom lane