Re: Why index is not using here?

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why index is not using here?
Date: 2010-02-16 11:44:22
Message-ID: 2a7905441002160344g354d12bdwd0a78b397252fe6e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

A mistake on the previous mail.

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

On Tue, Feb 16, 2010 at 5:43 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2010-02-16 12:45:58 Re: Almost infinite query -> Different Query Plan when changing where clause value
Previous Message AI Rumman 2010-02-16 11:43:03 Why index is not using here?