Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4536A632822 for ; Tue, 16 Feb 2010 07:43:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 00122-04 for ; Tue, 16 Feb 2010 11:43:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f196.google.com (mail-pz0-f196.google.com [209.85.222.196]) by mail.postgresql.org (Postfix) with ESMTP id 71604632180 for ; Tue, 16 Feb 2010 07:43:05 -0400 (AST) Received: by pzk34 with SMTP id 34so234724pzk.20 for ; Tue, 16 Feb 2010 03:43:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=v91se6oDoTWsxKwS+0dio2zMfa0YxYTIrTR/daYfwHc=; b=f1nZiK91YuhwhAmuKc8Q0Roy8Kw1kF80+5K2BtttsgMUN8vlDxdPqn5jbc2BvAkOiZ wOGdZHcoe3dytl849PLu7vUlf8dHdXd6gitCnWDfP5O91EI4UmFKiS5AhsG8q2/KrhY6 TwkNd8jLjeMU132AplGj4a98O+vMjtW/ujFkg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=QJX7eJtA673qZYuGO0CHsrzw2UQ3O3fU/qo4ARJusdx2/quyfi5HWQu17R62QQjHwh dxWjqukFnsRehxItXNwfU2xlqVDTXUvN6khWE7Nx6yQJO5JQ+JsedIa6qABnqfttVj06 BsGRiwgTiIGhRvb5kENo2/0smW18fmMMYdMUw= MIME-Version: 1.0 Received: by 10.140.255.10 with SMTP id c10mr4195242rvi.114.1266320583082; Tue, 16 Feb 2010 03:43:03 -0800 (PST) Date: Tue, 16 Feb 2010 17:43:03 +0600 Message-ID: <2a7905441002160343m2252b8f6sf14c63e1d6d04700@mail.gmail.com> Subject: Why index is not using here? From: AI Rumman To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd0ebbc86c2f6047fb63cb3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/252 X-Sequence-Number: 37673 --000e0cd0ebbc86c2f6047fb63cb3 Content-Type: text/plain; charset=ISO-8859-1 I am getting seq_scan on vtiger_account. Index is not using. Could anyone please tell me what the reason is? explain analyze select * from vtiger_account LEFT JOIN vtiger_account vtiger_account2 ON vtiger_account.parentid = vtiger_account2.accountid QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=12506.65..38690.74 rows=231572 width=264) (actual time=776.910..4407.233 rows=231572 loops=1) Hash Cond: ("outer".parentid = "inner".accountid) -> Seq Scan on vtiger_account (cost=0.00..7404.72 rows=231572 width=132) (actual time=0.029..349.195 rows=231572 loops=1) -> Hash (cost=7404.72..7404.72 rows=231572 width=132) (actual time=776.267..776.267 rows=231572 loops=1) -> Seq Scan on vtiger_account vtiger_account2 (cost=0.00..7404.72 rows=231572 width=132) (actual time=0.002..344.879 rows=231572 loops=1) Total runtime: 4640.868 ms (6 rows) vtigercrm504=# set enable_Seqscan = on; SET ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=0.00..1383629.28 rows=231572 width=264) (actual time=0.166..1924.417 rows=231572 loops=1) Merge Cond: ("outer".parentid = "inner".accountid) -> Index Scan using vtiger_account_parentid_idx on vtiger_account (cost=0.00..642475.34 rows=231572 width=132) (actual time=0.083..483.985 rows=231572 loops=1) -> Index Scan using vtiger_account_pkey on vtiger_account vtiger_account2 (cost=0.00..737836.61 rows=231572 width=132) (actual time=0.074..532.463 rows=300971 loops=1) Total runtime: 2140.326 ms (5 rows) \d vtiger_account Table "public.vtiger_account" Column | Type | Modifiers ---------------+------------------------+-------------------------------- accountid | integer | not null default 0 accountname | character varying(200) | not null parentid | integer | default 0 account_type | character varying(200) | industry | character varying(200) | annualrevenue | integer | default 0 rating | character varying(200) | ownership | character varying(50) | siccode | character varying(50) | tickersymbol | character varying(30) | phone | character varying(30) | otherphone | character varying(30) | email1 | character varying(100) | email2 | character varying(100) | website | character varying(100) | fax | character varying(30) | employees | integer | default 0 emailoptout | character varying(3) | default '0'::character varying notify_owner | character varying(3) | default '0'::character varying Indexes: "vtiger_account_pkey" PRIMARY KEY, btree (accountid) "account_account_type_idx" btree (account_type) "vtiger_account_parentid_idx" btree (parentid) --000e0cd0ebbc86c2f6047fb63cb3 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
I am getting seq_scan on vtiger_account. Index is not using.
Could anyone please tell me what the reason is?
=A0
=A0
=A0explain=A0=A0 analyze=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=A0=A0=A0=A0=A0=A0=A0
select *=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=A0=A0=A0=A0=A0=A0=A0=A0=A0
from vt= iger_account=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
LEFT= JOIN vtiger_account vtiger_account2=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 ON vtiger_account.parentid =3D vtiger_account2.accoun= tid=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=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=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=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
-------------------= ---------------------------------------------------------------------------= -------------------------------------------------------
=A0Hash Left Join=A0 (cost=3D12506.65..38690.74 rows=3D231572 width=3D264) = (actual time=3D776.910..4407.233 rows=3D231572 loops=3D1)
=A0=A0 Hash Co= nd: ("outer".parentid =3D "inner".accountid)
=A0=A0 = ->=A0 Seq Scan on vtiger_account=A0 (cost=3D0.00..7404.72 rows=3D231572 = width=3D132) (actual time=3D0.029..349.195 rows=3D231572 loops=3D1)
=A0=A0 ->=A0 Hash=A0 (cost=3D7404.72..7404.72 rows=3D231572 width=3D132)= (actual time=3D776.267..776.267 rows=3D231572 loops=3D1)
=A0=A0=A0=A0= =A0=A0=A0=A0 ->=A0 Seq Scan on vtiger_account vtiger_account2=A0 (cost= =3D0.00..7404.72 rows=3D231572 width=3D132) (actual time=3D0.002..344.879 r= ows=3D231572 loops=3D1)
=A0Total runtime: 4640.868 ms
(6 rows)

vtigercrm504=3D# set enable_Seqscan =3D on;
SET


--------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------
=A0Merge Left Join=A0 (cost=3D0.00..13836= 29.28 rows=3D231572 width=3D264) (actual time=3D0.166..1924.417 rows=3D2315= 72 loops=3D1)
=A0=A0 Merge Cond: ("outer".parentid =3D "inner".accoun= tid)
=A0=A0 ->=A0 Index Scan using vtiger_account_parentid_idx on vti= ger_account=A0 (cost=3D0.00..642475.34 rows=3D231572 width=3D132) (actual t= ime=3D0.083..483.985 rows=3D231572 loops=3D1)
=A0=A0 ->=A0 Index Scan using vtiger_account_pkey on vtiger_account vtig= er_account2=A0 (cost=3D0.00..737836.61 rows=3D231572 width=3D132) (actual t= ime=3D0.074..532.463 rows=3D300971 loops=3D1)
=A0Total runtime: 2140.326= ms
(5 rows)

=A0

\d vtiger_account
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 Table "public.vtiger_account"
=A0=A0=A0 Column=A0= =A0=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 Modifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
= ---------------+------------------------+-------------------------------- =A0accountid=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 | not null default 0
=A0accountname=A0=A0 | character varying(200= ) | not null
=A0parentid=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 | default 0
=A0account_type=A0 | character vary= ing(200) |
=A0industry=A0=A0=A0=A0=A0 | character varying(200) |
=A0annualrevenue | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | d= efault 0
=A0rating=A0=A0=A0=A0=A0=A0=A0 | character varying(200) |
= =A0ownership=A0=A0=A0=A0 | character varying(50)=A0 |
=A0siccode=A0=A0= =A0=A0=A0=A0 | character varying(50)=A0 |
=A0tickersymbol=A0 | characte= r varying(30)=A0 |
=A0phone=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(30)=A0 |
=A0otherp= hone=A0=A0=A0 | character varying(30)=A0 |
=A0email1=A0=A0=A0=A0=A0=A0= =A0 | character varying(100) |
=A0email2=A0=A0=A0=A0=A0=A0=A0 | charact= er varying(100) |
=A0website=A0=A0=A0=A0=A0=A0 | character varying(100)= |
=A0fax=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(30)=A0 |
=A0em= ployees=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = | default 0
=A0emailoptout=A0=A0 | character varying(3)=A0=A0 | default = '0'::character varying
=A0notify_owner=A0 | character varying(3)= =A0=A0 | default '0'::character varying
Indexes:
=A0=A0=A0 "vtiger_account_pkey" PRIMARY KEY, btree (a= ccountid)
=A0=A0=A0 "account_account_type_idx" btree (account_= type)
=A0=A0=A0 "vtiger_account_parentid_idx" btree (parentid)=

--000e0cd0ebbc86c2f6047fb63cb3--