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
