pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery plan question, nested loop vs hash join
8+ messages / 4 participants
[nested] [flat]
* query plan question, nested loop vs hash join
@ 2014-10-03 15:38 Andrey Lizenko <lizenko79@gmail.com>
2014-10-07 20:26 ` Re: query plan question, nested loop vs hash join Marti Raudsepp <marti@juffo.org>
0 siblings, 1 reply; 8+ messages in thread
From: Andrey Lizenko @ 2014-10-03 15:38 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have similar problem as in
http://www.postgresql.org/message-id/flat/52B311C4.1070108@gmail.com#52B311C4.1070108@gmail.com
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-03 15:38 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
@ 2014-10-07 20:26 ` Marti Raudsepp <marti@juffo.org>
2014-10-08 09:48 ` Re: query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Marti Raudsepp @ 2014-10-07 20:26 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: pgsql-performance
On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko <lizenko79@gmail.com> wrote:
> 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.
Have you tried changing random_page_cost?
In small databases where most of the data is cached anyway, lowering
random_page_cost to somewhere between 1 and 2 usually leads to better
planner decisions.
Regards,
Marti
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-03 15:38 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
2014-10-07 20:26 ` Re: query plan question, nested loop vs hash join Marti Raudsepp <marti@juffo.org>
@ 2014-10-08 09:48 ` Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andrey Lizenko @ 2014-10-08 09:48 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: pgsql-performance
Thanks for your reply, Marti, as I answered to Tom couple of days ago
adjusting of 'effective_cache_size' to 80% of RAM and 'random_page_cost'
from 2 to 1 helped me.
On 8 October 2014 00:26, Marti Raudsepp <marti@juffo.org> wrote:
> On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko <lizenko79@gmail.com>
> wrote:
> > 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.
>
> Have you tried changing random_page_cost?
>
> In small databases where most of the data is cached anyway, lowering
> random_page_cost to somewhere between 1 and 2 usually leads to better
> planner decisions.
>
> Regards,
> Marti
>
--
С уважением, Андрей Лизенко
^ permalink raw reply [nested|flat] 8+ messages in thread
* query plan question, nested loop vs hash join
@ 2014-10-05 18:57 Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 19:18 ` Re: query plan question, nested loop vs hash join Victor Yegorov <vyegorov@gmail.com>
2014-10-05 19:47 ` Re: query plan question, nested loop vs hash join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Andrey Lizenko @ 2014-10-05 18:57 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have similar problem as in
http://www.postgresql.org/message-id/flat/52B311C4.1070108@gmail.com#52B311C4.1070108@gmail.com
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-05 18:57 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
@ 2014-10-05 19:18 ` Victor Yegorov <vyegorov@gmail.com>
2014-10-06 12:58 ` Re: query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Victor Yegorov @ 2014-10-05 19:18 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-05 18:57 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 19:18 ` Re: query plan question, nested loop vs hash join Victor Yegorov <vyegorov@gmail.com>
@ 2014-10-06 12:58 ` Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andrey Lizenko @ 2014-10-06 12:58 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-performance
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
>
--
С уважением, Андрей Лизенко
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-05 18:57 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
@ 2014-10-05 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-06 12:50 ` Re: query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2014-10-05 19:47 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan question, nested loop vs hash join
2014-10-05 18:57 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 19:47 ` Re: query plan question, nested loop vs hash join Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-10-06 12:50 ` Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andrey Lizenko @ 2014-10-06 12:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
>
--
С уважением, Андрей Лизенко
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-10-08 09:48 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-03 15:38 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
2014-10-07 20:26 ` Marti Raudsepp <marti@juffo.org>
2014-10-08 09:48 ` Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 18:57 query plan question, nested loop vs hash join Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 19:18 ` Victor Yegorov <vyegorov@gmail.com>
2014-10-06 12:58 ` Andrey Lizenko <lizenko79@gmail.com>
2014-10-05 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-06 12:50 ` Andrey Lizenko <lizenko79@gmail.com>
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