From klodoma@ar-sd.net Thu Dec 4 14:55:53 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 64D61D1B468 for ; Thu, 4 Dec 2003 14:55:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44395-09 for ; Thu, 4 Dec 2003 10:55:26 -0400 (AST) Received: from ar-sd.net (unknown [81.196.32.112]) by svr1.postgresql.org (Postfix) with ESMTP id 105B6D1B432 for ; Thu, 4 Dec 2003 10:55:22 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 7C9DD1D021 for ; Thu, 4 Dec 2003 16:58:37 +0200 (EET) Received: from andy (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 562D61CCD8 for ; Thu, 4 Dec 2003 16:58:37 +0200 (EET) Message-ID: <014901c3ba76$ffd65650$0b00a8c0@andy> From: "Andrei Bintintan" To: Subject: Index not used. WHY? Date: Thu, 4 Dec 2003 16:57:51 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0146_01C3BA87.C0BC67F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN, HTML_FONT_COLOR_RED, HTML_FONT_COLOR_UNSAFE X-Spam-Level: X-Archive-Number: 200312/35 X-Sequence-Number: 11543 This is a multi-part message in MIME format. ------=_NextPart_000_0146_01C3BA87.C0BC67F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi,=20 I have the following table: CREATE TABLE public.rights ( id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL,=20 id_user int4 NOT NULL,=20 id_modull int4 NOT NULL,=20 CONSTRAINT rights_pkey PRIMARY KEY (id) )=20 and I created the following indexes: CREATE INDEX right_id_modull_idx ON rights USING btree (id_modull); CREATE INDEX right_id_user_idx ON rights USING btree (id_user); Now the problem: EXPLAIN SELECT * FROM rights r WHERE r.id_modull =3D15 returnes: Seq Scan on rights r (cost=3D0.00..12.30 rows=3D42 width=3D12) Filter: (id_modull =3D 15) EXPLAIN SELECT * FROM rights r WHERE r.id_user =3D15 returnes: Index Scan using right_id_user_idx on rights r (cost=3D0.00..8.35 rows=3D11= width=3D12) Index Cond: (id_user =3D 15) Question: Why the right_id_modull_idx is NOT USED at the 1st query and the = second query the right_id_user_idx index is used.=20 I don't understand this.=20 Thanx in advance. Andy. ------=_NextPart_000_0146_01C3BA87.C0BC67F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have the following table:

CREATE TABLE public.rights (
id int4 DEFAULT=20 nextval('"rights_id_seq"'::text) NOT NULL,
= id_user int4 NOT NULL,
= id_modull int4 NOT NULL,
= CONSTRAINT rights_pkey PRIMARY KEY (id)
)=20

and I created the following indexes:<= /P>

CREATE INDEX right_id_modull_idx ON=20 rights USING btree (id_modull);
CREATE
INDEX right_id_u= ser_idx=20 ON rights USING b= tree=20 (id_user);

Now the problem:

EXPLAIN SELECT * FROM rights r WHERE r.id_modull =3D15
returnes:
Seq Scan on= =20 rights r (cost=3D0.00..12.30 rows=3D42 width=3D12)
Filter: (id_modull = =3D=20 15)

EXPLAIN SELECT * FROM rights r WHERE r.id_user =3D15
returnes:
Index Scan using right_id_user_idx on=20 rights r (cost=3D0.00..8.35 rows=3D11 width=3D12)
= Index=20 Cond: (id_user =3D 15)

Question: Why the right_id_modull_idx is NOT US= ED at=20 the 1st query and the second query the right_id_user_idx index is used.=20

I don't understand this.

Thanx in=20 advance.
Andy.

 

------=_NextPart_000_0146_01C3BA87.C0BC67F0-- From sszabo@megazone.bigpanda.com Thu Dec 4 15:20:16 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BB9F1D1B4B1; Thu, 4 Dec 2003 15:20:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53565-08; Thu, 4 Dec 2003 11:19:49 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id EBAEBD1B468; Thu, 4 Dec 2003 11:19:44 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3E7E535500; Thu, 4 Dec 2003 07:19:49 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3CE3935437; Thu, 4 Dec 2003 07:19:49 -0800 (PST) Date: Thu, 4 Dec 2003 07:19:49 -0800 (PST) From: Stephan Szabo To: Andrei Bintintan Cc: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Index not used. WHY? In-Reply-To: <014901c3ba76$ffd65650$0b00a8c0@andy> Message-ID: <20031204071432.R66123@megazone.bigpanda.com> References: <014901c3ba76$ffd65650$0b00a8c0@andy> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/37 X-Sequence-Number: 11545 On Thu, 4 Dec 2003, Andrei Bintintan wrote: > Hi, > > I have the following table: > CREATE TABLE public.rights ( > id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL, > id_user int4 NOT NULL, > id_modull int4 NOT NULL, > CONSTRAINT rights_pkey PRIMARY KEY (id) > ) > > and I created the following indexes: > > CREATE INDEX right_id_modull_idx ON rights USING btree (id_modull); > CREATE INDEX right_id_user_idx ON rights USING btree (id_user); > > Now the problem: > > EXPLAIN SELECT * FROM rights r WHERE r.id_modull =15 > returnes: > Seq Scan on rights r (cost=0.00..12.30 rows=42 width=12) > Filter: (id_modull = 15) > > EXPLAIN SELECT * FROM rights r WHERE r.id_user =15 > returnes: > Index Scan using right_id_user_idx on rights r (cost=0.00..8.35 rows=11 width=12) > Index Cond: (id_user = 15) > > Question: Why the right_id_modull_idx is NOT USED at the 1st query and > the second query the right_id_user_idx index is used. As a note, pgsql-performance is a better list for these questions. So, standard questions: How many rows are in the table, what does EXPLAIN ANALYZE show for the queries, if you force index usage (set enable_seqscan=off) on the first what does EXPLAIN ANALYZE show then, have you used ANALYZE/VACUUM ANALYZE recently? From klodoma@ar-sd.net Fri Dec 5 08:09:13 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7561CD1B460 for ; Fri, 5 Dec 2003 08:09:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03299-10 for ; Fri, 5 Dec 2003 04:08:43 -0400 (AST) Received: from ar-sd.net (unknown [81.196.32.112]) by svr1.postgresql.org (Postfix) with ESMTP id 81837D1B474 for ; Fri, 5 Dec 2003 04:08:40 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 262561C8D4; Fri, 5 Dec 2003 10:11:48 +0200 (EET) Received: from andy (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 055C917723; Fri, 5 Dec 2003 10:11:48 +0200 (EET) Message-ID: <00c901c3bb07$592bbe30$0b00a8c0@andy> From: "Andrei Bintintan" To: "Stephan Szabo" Cc: References: <014901c3ba76$ffd65650$0b00a8c0@andy> <20031204071432.R66123@megazone.bigpanda.com> Subject: Re: [ADMIN] Index not used. WHY? Date: Fri, 5 Dec 2003 10:11:11 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/117 X-Sequence-Number: 4977 There are around 700 rows in this table. If I set enable_seqscan=off then the index is used and I also used Vacuum Analyze recently. I find it strange because the number of values of id_user and id_modull are somehow in the same distribution and when I search the table the id_user index is used but the id_modull index is not used. Does somehow postgre know that a seq scan runs faster in this case as a index scan? Should I erase this index? I have to say that the data's in this table are not changed offen, but there are a LOT of joins made with this table. Best regards. Andy. ----- Original Message ----- From: "Stephan Szabo" To: "Andrei Bintintan" Cc: ; Sent: Thursday, December 04, 2003 5:19 PM Subject: Re: [ADMIN] Index not used. WHY? > > On Thu, 4 Dec 2003, Andrei Bintintan wrote: > > > Hi, > > > > I have the following table: > > CREATE TABLE public.rights ( > > id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL, > > id_user int4 NOT NULL, > > id_modull int4 NOT NULL, > > CONSTRAINT rights_pkey PRIMARY KEY (id) > > ) > > > > and I created the following indexes: > > > > CREATE INDEX right_id_modull_idx ON rights USING btree (id_modull); > > CREATE INDEX right_id_user_idx ON rights USING btree (id_user); > > > > Now the problem: > > > > EXPLAIN SELECT * FROM rights r WHERE r.id_modull =15 > > returnes: > > Seq Scan on rights r (cost=0.00..12.30 rows=42 width=12) > > Filter: (id_modull = 15) > > > > EXPLAIN SELECT * FROM rights r WHERE r.id_user =15 > > returnes: > > Index Scan using right_id_user_idx on rights r (cost=0.00..8.35 rows=11 width=12) > > Index Cond: (id_user = 15) > > > > Question: Why the right_id_modull_idx is NOT USED at the 1st query and > > the second query the right_id_user_idx index is used. > > As a note, pgsql-performance is a better list for these questions. > > So, standard questions: > > How many rows are in the table, what does EXPLAIN ANALYZE show for the > queries, if you force index usage (set enable_seqscan=off) on the first > what does EXPLAIN ANALYZE show then, have you used ANALYZE/VACUUM ANALYZE > recently? > From shridhar_daithankar@myrealbox.com Fri Dec 5 08:40:37 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0F5C4D1B47A for ; Fri, 5 Dec 2003 08:40:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14504-02 for ; Fri, 5 Dec 2003 04:40:07 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id D8495D1B475 for ; Fri, 5 Dec 2003 04:40:04 -0400 (AST) Received: from myrealbox.com shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.45 $ on Novell NetWare via secured & encrypted transport (TLS); Fri, 05 Dec 2003 01:40:07 -0700 Message-ID: <3FD04457.6000504@myrealbox.com> Date: Fri, 05 Dec 2003 14:09:51 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Andrei Bintintan Cc: Stephan Szabo , pgsql-performance@postgresql.org Subject: Re: [ADMIN] Index not used. WHY? References: <014901c3ba76$ffd65650$0b00a8c0@andy> <20031204071432.R66123@megazone.bigpanda.com> <00c901c3bb07$592bbe30$0b00a8c0@andy> In-Reply-To: <00c901c3bb07$592bbe30$0b00a8c0@andy> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/118 X-Sequence-Number: 4978 Andrei Bintintan wrote: > There are around 700 rows in this table. > If I set enable_seqscan=off then the index is used and I also used Vacuum > Analyze recently. For 700 rows I think seq. would work best. > > I find it strange because the number of values of id_user and id_modull are > somehow in the same distribution and when I search the table the id_user > index is used but the id_modull index is not used. > > Does somehow postgre know that a seq scan runs faster in this case as a > index scan? Should I erase this index? > I have to say that the data's in this table are not changed offen, but there > are a LOT of joins made with this table. If table is cached then it does not matter. Unless it grows substantially, say to around hundred thousand rows(Note your table is small), idex wouldn't be that useful. Shridhar From sszabo@megazone.bigpanda.com Fri Dec 5 15:35:27 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D3C5AD1B459 for ; Fri, 5 Dec 2003 15:35:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 73756-03 for ; Fri, 5 Dec 2003 11:35:00 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 20755D1B43A for ; Fri, 5 Dec 2003 11:34:56 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 867E3355D4; Fri, 5 Dec 2003 07:34:45 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 85311355A1; Fri, 5 Dec 2003 07:34:45 -0800 (PST) Date: Fri, 5 Dec 2003 07:34:45 -0800 (PST) From: Stephan Szabo To: Andrei Bintintan Cc: pgsql-performance@postgresql.org Subject: Re: [ADMIN] Index not used. WHY? In-Reply-To: <00c901c3bb07$592bbe30$0b00a8c0@andy> Message-ID: <20031205072359.X5253@megazone.bigpanda.com> References: <014901c3ba76$ffd65650$0b00a8c0@andy> <20031204071432.R66123@megazone.bigpanda.com> <00c901c3bb07$592bbe30$0b00a8c0@andy> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/126 X-Sequence-Number: 4986 On Fri, 5 Dec 2003, Andrei Bintintan wrote: > There are around 700 rows in this table. > If I set enable_seqscan=off then the index is used and I also used Vacuum > Analyze recently. > > I find it strange because the number of values of id_user and id_modull are > somehow in the same distribution and when I search the table the id_user > index is used but the id_modull index is not used. It was guessing that one would return 11 rows and the other 42 which is why one used the index and the other wouldn't. If those numbers aren't realistic, you may want to raise the statistics target for the columns (see ALTER TABLE) and re-run analyze. > Does somehow postgre know that a seq scan runs faster in this case as a > index scan? Should I erase this index? It's making an educated guess. When you're doing an index scan, it needs to read through the index and then get matching rows from the table. However, because those reads from the table are in a potentially random order, there's usually a higher cost associated with those reads than if the table was read in order (barring cases where you know your database should always stay cached in disk cache, etc...). If there's say 50 pages in the entire table, a sequence scan does 50 sequential page reads and is checking all those tuples. If you're getting say 42 rows through an index, you're first reading through the index, and then getting pages in a random order from the table where depends on the distribution of values throughout the table. There's a variable in the configuration, random_page_cost which controls the ratio of cost between a sequential read and a random one (defaulting to 4).