+++ This bug was initially created as a clone of Bug #1302794 +++

Description of problem:
storage_domains generate unefficient query caused by 'Hash Left Join  (cost=17.840..30.040 rows=14 width=1886) (actual time=422.963..5452.542 rows=14 loops=1)'


#	exclusive	inclusive	rows x	rows	loops	node
1.	5,452.362	5,452.542	↑ 1.0	14	1	
→
Hash Left Join (cost=17.840..30.040 rows=14 width=1,886) (actual time=422.963..5,452.542 rows=14 loops=1)
Hash Cond: (hotel.quebec = uniform.seven)
2.	0.024	0.133	↑ 1.0	14	1	
→
Hash Left Join (cost=15.370..16.890 rows=14 width=1,768) (actual time=0.052..0.133 rows=14 loops=1)
Hash Cond: (hotel.quebec = sierra.delta)
3.	0.067	0.106	↑ 1.0	14	1	
→
Hash Join (cost=1.320..2.650 rows=14 width=1,752) (actual time=0.043..0.106 rows=14 loops=1)
Hash Cond: (hotel.quebec = whiskey.quebec)
4.	0.017	0.017	↑ 1.0	14	1	
→
Seq Scan on hotel (cost=0.000..1.140 rows=14 width=1,740) (actual time=0.006..0.017 rows=14 loops=1)
5.	0.012	0.022	↑ 1.0	14	1	
→
Hash (cost=1.140..1.140 rows=14 width=28) (actual time=0.022..0.022 rows=14 loops=1)
6.	0.010	0.010	↑ 1.0	14	1	
→
Seq Scan on whiskey (cost=0.000..1.140 rows=14 width=28) (actual time=0.007..0.010 rows=14 loops=1)
7.	0.001	0.003	↓ 0.0	0	1	
→
Hash (cost=12.930..12.930 rows=90 width=16) (actual time=0.003..0.003 rows=0 loops=1)
8.	0.002	0.002	↓ 0.0	0	1	
→
HashAggregate (cost=11.120..12.030 rows=90 width=16) (actual time=0.002..0.002 rows=0 loops=1)
9.	0.000	0.000	↓ 0.0	0	1	
→
Seq Scan on sierra (cost=0.000..10.900 rows=90 width=16) (actual time=0.000..0.000 rows=0 loops=1)
10.	0.006	0.047	↑ 1.0	12	1	
→
Hash (cost=2.330..2.330 rows=12 width=134) (actual time=0.047..0.047 rows=12 loops=1)
11.	0.020	0.041	↑ 1.0	12	1	
→
Hash Left Join (cost=1.040..2.330 rows=12 width=134) (actual time=0.026..0.041 rows=12 loops=1)
Hash Cond: (uniform.golf = romeo.quebec)
12.	0.006	0.006	↑ 1.0	12	1	
→
Seq Scan on uniform (cost=0.000..1.120 rows=12 width=36) (actual time=0.002..0.006 rows=12 loops=1)
13.	0.011	0.015	↑ 1.0	2	1	
→
Hash (cost=1.020..1.020 rows=2 width=114) (actual time=0.015..0.015 rows=2 loops=1)
14.	0.004	0.004	↑ 1.0	2	1	
→
Seq Scan on romeo (cost=0.000..1.020 rows=2 width=114) (actual time=0.002..0.004 rows=2 loops=1)

Version-Release number of selected component (if applicable):
3.6.2

How reproducible:
100 % 

Steps to Reproduce:
1. run this query on large scale env.

Actual results:
"Hash Left Join  (cost=17.84..30.04 rows=14 width=1886) (actual time=417.726..5394.149 rows=14 loops=1)"
"  Hash Cond: (storage_domain_static.id = storage_pool_iso_map.storage_id)"

takes ~5 sec.

Expected results:
fast execution time, may be using sub query or other join implementation

Additional info:

--- Additional comment from Eldad Marciano on 2016-01-31 13:13:32 IST ---


the most heavy part generated by storage_domains view

and these lines is the most expensive parts:

select storage_domain_static.id, ( select fn_get_disk_commited_value_by_storage(storage_domain_static.id) AS commited_disk_size ) from storage_domain_static

"Seq Scan on storage_domain_static  (cost=0.00..4.64 rows=14 width=16) (actual time=204.207..2843.699 rows=14 loops=1)"
"  Output: id, fn_get_disk_commited_value_by_storage(id)"
"Total runtime: 2843.724 ms

--- Additional comment from Allon Mureinik on 2016-01-31 21:07:40 IST ---

Thanks for the report. A few questions though:

1. How many rows do you have in this view? 
1.a. Preferably, if you could attach a dump of the database, it would be awesome.
2. On what hardware/load was this result observed?
3. The expect result is "fast execution time" - can you assign a numeric value to fast? 1 sec? 1 msec?

--- Additional comment from Eldad Marciano on 2016-02-01 12:28:53 IST ---

(In reply to Allon Mureinik from comment #2)
> Thanks for the report. A few questions though:
> 
> 1. How many rows do you have in this view? 
14

> 1.a. Preferably, if you could attach a dump of the database, it would be
> awesome.
I will to arrange that, or may be provide you access.

> 2. On what hardware/load was this result observed?
24 cores 120Gb ram.

> 3. The expect result is "fast execution time" - can you assign a numeric
> value to fast? 1 sec? 1 msec?
we currently monitor SQL duration which longer than 3 sec.
lets try to make it faster as we can.

--- Additional comment from Eldad Marciano on 2016-04-21 13:31:42 IDT ---

any chance to backport it ?

--- Additional comment from Eldad Marciano on 2016-04-21 13:40:22 IDT ---

this view has big impact on these two functions, which they called quite a lot

getstorage_domains_by_storagepoolid
getstorage_domains_list_by_storagedomainid

see the following report:
https://mojo.redhat.com/docs/DOC-1076059

--- Additional comment from Allon Mureinik on 2016-04-21 15:04:49 IDT ---

(In reply to Eldad Marciano from comment #4)
> any chance to backport it ?
Given triple acks, I don't think there should be a problem. Eli - keep me honest here - this patch creates a new view, rewrites some existing ones, and removes a stored procedure.
Should there be a problem with backporting that wrt shcema upgrades?

--- Additional comment from Eli Mesika on 2016-05-02 17:11:35 IDT ---

(In reply to Allon Mureinik from comment #6)
> (In reply to Eldad Marciano from comment #4)
> > any chance to backport it ?
> Given triple acks, I don't think there should be a problem. Eli - keep me
> honest here - this patch creates a new view, rewrites some existing ones,
> and removes a stored procedure.
> Should there be a problem with backporting that wrt shcema upgrades?

As long as the views returns teh same result set , I see no problem in back porting that
=============================================================================

This BZ is a 3.6.z clone to clone the patch's backport