Thread: query plan question, nested loop vs hash join
Hi,
I have similar problem as in
server version is 9.3.4
Here is only two quite simple tables:
db_new=# \d activities_example
Table "public.activities_example"
Column | Type | Modifiers
----------------+---------+-----------
id | integer |
order_chain_id | integer |
Indexes:
"activities_example_idx" btree (order_chain_id)
db_new=# \d orders_example
Table "public.orders_example"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Number of rows as below:
db_new=# select count(*) from activities_example ;
count
---------
3059965
db_new=# select count(*) from orders_example ;
count
-------
19038
db_new=# select count(*) from activities_example where order_chain_id in (select id from orders_example);
count
-------
91426
(1 row)
and I can see that planner uses hashjoin with all enabled options and nested loop with disabled parameter:
db_new=# explain analyze select * from activities_example where order_chain_id in (select id from orders_example);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=513.36..57547.59 rows=89551 width=8) (actual time=18.340..966.367 rows=91426 loops=1)
Hash Cond: (activities_example.order_chain_id = orders_example.id)
-> Seq Scan on activities_example (cost=0.00..44139.65 rows=3059965 width=8) (actual time=0.018..294.216 rows=3059965 loops=1)
-> Hash (cost=275.38..275.38 rows=19038 width=4) (actual time=5.458..5.458 rows=19038 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 670kB
-> Seq Scan on orders_example (cost=0.00..275.38 rows=19038 width=4) (actual time=0.015..2.308 rows=19038 loops=1)
Total runtime: 970.234 ms
(7 rows)
db_new=# set enable_hashjoin = off;
SET
db_new=# explain analyze select * from activities_example where order_chain_id in (select id from orders_example);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1629.09..166451.01 rows=89551 width=8) (actual time=16.091..116.476 rows=91426 loops=1)
-> Unique (cost=1628.66..1723.85 rows=19038 width=4) (actual time=15.929..23.156 rows=19038 loops=1)
-> Sort (cost=1628.66..1676.25 rows=19038 width=4) (actual time=15.892..19.884 rows=19038 loops=1)
Sort Key: orders_example.id
Sort Method: external sort Disk: 264kB
-> Seq Scan on orders_example (cost=0.00..275.38 rows=19038 width=4) (actual time=0.015..2.747 rows=19038 loops=1)
-> Index Scan using activities_example_idx on activities_example (cost=0.43..8.60 rows=5 width=8) (actual time=0.002..0.004 rows=5 loops=19038)
Index Cond: (order_chain_id = orders_example.id)
Total runtime: 121.366 ms
(9 rows)
second runtime is much more quicker.
What is the reason of "Seq Scan on activities_example" in the first case?
Is it possible to force optimizer choose the second plan without doing "set enable_hashjoin = off;" ?
Increasing of 'effective_cache_size' leads to similar thing with mergejoin,
other options (work_mem, shared_buffers. etc) do not change anything.
Thanks in advance.
Regards, Andrey Lizenko
2014-10-05 21:57 GMT+03:00 Andrey Lizenko <lizenko79@gmail.com>:
Increasing of 'effective_cache_size' leads to similar thing with mergejoin,other options (work_mem, shared_buffers. etc) do not change anything.
I think increasing `work_mem` should have effects, as plan with `Nested Loop` is using disk-based sort.
Increase it till you'll stop seeing `external sort` in the EXPLAIN output. Something like '10MB' should do.
Also, it'd be handy if you could provide `EXPLAIN (analyze, buffers)` output along with the results of these queries:
SELECT name,setting,source FROM pg_settings WHERE name ~ 'cost' AND NOT name ~ 'vacuum';
SELECT name,setting,source FROM pg_settings WHERE NOT source IN ('default','override');
And describe your setup: what OS? how much RAM? what kind of disks? RAID?
--
Victor Y. Yegorov
And describe your setup: what OS? how much RAM? what kind of disks? RAID?
Victor Y. Yegorov
Andrey Lizenko <lizenko79@gmail.com> writes: > What is the reason of "Seq Scan on activities_example" in the first case? > Is it possible to force optimizer choose the second plan without doing > "set enable_hashjoin = off;" ? Disabling hashjoins altogether would be a pretty dangerous "fix". I think the real issue here is that you have an entirely cached-in-memory database and therefore you ought to reduce random_page_cost. The planner's estimates for the first query seem to more or less match reality (on the assumption that 1 msec equals about 100 cost units on your machine). The cost estimates for the second one are way off though, mainly in that the repeated indexscans are far cheaper than the planner thinks. Getting that cost estimate down requires reducing random_page_cost or increasing effective_cache_size or some combination. You can find the conventional wisdow about this sort of thing at https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server regards, tom lane
Thanks a lot, Tom,
reducing 'random_page_cost' from 2 to 1 and increasing 'effective_cache_size' from 70% to 80% of RAM solved this at least on my virtual sandbox.
By the way, why increasing of cache only (with the same random_page_cost=2) can lead to mergejoin selection?
On 5 October 2014 23:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrey Lizenko <lizenko79@gmail.com> writes:
> What is the reason of "Seq Scan on activities_example" in the first case?
> Is it possible to force optimizer choose the second plan without doing
> "set enable_hashjoin = off;" ?
Disabling hashjoins altogether would be a pretty dangerous "fix".
I think the real issue here is that you have an entirely cached-in-memory
database and therefore you ought to reduce random_page_cost. The
planner's estimates for the first query seem to more or less match reality
(on the assumption that 1 msec equals about 100 cost units on your
machine). The cost estimates for the second one are way off though,
mainly in that the repeated indexscans are far cheaper than the planner
thinks. Getting that cost estimate down requires reducing random_page_cost
or increasing effective_cache_size or some combination.
You can find the conventional wisdow about this sort of thing at
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
regards, tom lane
С уважением, Андрей Лизенко
As I answered to Tom few moments ago:
>reducing 'random_page_cost' from 2 to 1 and increasing 'effective_cache_size' from 70% to 80% of RAM solved this at least on my virtual sandbox.
I've observed same behaviour both on weak virtual machine and on the quite powerfull stress test platform.
The first one is Ubuntu 12.04 LTS, second one is RedHat 6.4
Of course, RAM. RAID, CPUs and so on are different enough, so I believe the root clause of this issue is not connected with hardware at all.
Thanks for your idea with external sort, I'll test it
On 5 October 2014 23:18, Victor Yegorov <vyegorov@gmail.com> wrote:
2014-10-05 21:57 GMT+03:00 Andrey Lizenko <lizenko79@gmail.com>:Increasing of 'effective_cache_size' leads to similar thing with mergejoin,other options (work_mem, shared_buffers. etc) do not change anything.
I think increasing `work_mem` should have effects, as plan with `Nested Loop` is using disk-based sort.Increase it till you'll stop seeing `external sort` in the EXPLAIN output. Something like '10MB' should do.Also, it'd be handy if you could provide `EXPLAIN (analyze, buffers)` output along with the results of these queries:SELECT name,setting,source FROM pg_settings WHERE name ~ 'cost' AND NOT name ~ 'vacuum';SELECT name,setting,source FROM pg_settings WHERE NOT source IN ('default','override');
And describe your setup: what OS? how much RAM? what kind of disks? RAID?--
Victor Y. Yegorov
С уважением, Андрей Лизенко