Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group