Обсуждение: Why index is not using here?

                                                                     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)

AI Rumman <rummandba@gmail.com> wrote:

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

It's doing over half a million random accesses in less than two
seconds, which suggests rather strongly to me that your data is
cached.  Unless you have tuned the costing configuration values such
that the optimizer has reasonable information about this, you're not
going to get the fastest plans for this environment.  (The plan it
generated would be a great plan if you were actually going to disk
for all of this.)  Without knowing more about the machine on which
you're running this, it's hard to guess at optimal settings, but you
almost certainly need to adjust random_page_cost, seq_page_cost, and
effective_cache_size; and possibly others.

Please post information about your OS, CPUs, RAM, and disk system.

As a complete SWAG, you could try setting these (instead of
disabling seqscan):

set random_page_cost = 0.01;
set seq_page_cost = 0.01;
set effective_cache_size = '6GB';

-Kevin