pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy index is not using here?
3+ messages / 2 participants
[nested] [flat]
* Why index is not using here?
@ 2010-02-16 11:43 AI Rumman <rummandba@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: AI Rumman @ 2010-02-16 11:43 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why index is not using here?
@ 2010-02-16 11:44 AI Rumman <rummandba@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: AI Rumman @ 2010-02-16 11:44 UTC (permalink / raw)
To: 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@gmail.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)
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why index is not using here?
@ 2010-02-16 14:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: AI Rumman <rummandba@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2010-02-16 14:56 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-02-16 14:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-16 11:43 Why index is not using here? AI Rumman <rummandba@gmail.com>
2010-02-16 11:44 ` AI Rumman <rummandba@gmail.com>
2010-02-16 14:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox