SELECT idblog, obp
FROM   
	(
		SELECT blog_id, MAX (end_date) AS end_date
		FROM (
			(
				SELECT 
					_blog.id AS blog_id, 
					(xpath_string (_product_billing_tmp.data, '//data/end_date'))::date
						AS end_date
				FROM   _product_association
				LEFT JOIN _product_billing_tmp
					ON  _product_association.id = _product_billing_tmp.parent_id
				INNER JOIN _blog
					ON _blog.id = _product_association.parent_id	
				WHERE _product_billing_tmp.path <@ '0.1'
				AND   _product_billing_tmp.state = 1
				AND _product_association.product_id = 6		
			)
			UNION 
			(
				SELECT 
					_blog.id AS blog_id, 
					(xpath_string (_product_billing.data, '//data/end_date'))::date
						AS end_date
				FROM   _product_association
				LEFT JOIN _product_billing
					ON  _product_association.id = _product_billing.parent_id
				INNER JOIN _blog
					ON _blog.id = _product_association.parent_id	
				WHERE _product_billing.path <@ '0.1'
				AND _product_association.product_id = 6		
			)
			UNION
			(
				SELECT 
					_blog.id AS blog_id, 
					_product_association.date_creation::date AS end_date
				FROM   _product_association
				INNER JOIN _blog
					ON _blog.id = _product_association.parent_id	
				WHERE _product_association.path <@ '0.1'
				AND _product_association.product_id = 6		
			)) AS end_date_select
		GROUP BY blog_id
	) AS all_end_date,
	stats_day_blog
WHERE stats_day_blog.idblog = all_end_date.blog_id
AND day >  all_end_date.end_date


------------------------------------------------------------------------------------------------------------

QUERY PLAN

Nested Loop  (cost=13554.81..79209.10 rows=23220 width=12)

  ->  GroupAggregate  (cost=13554.81..13604.15 rows=200 width=12)

        ->  Unique  (cost=13554.81..13570.42 rows=2082 width=467)

              ->  Sort  (cost=13554.81..13560.01 rows=2082 width=467)

                    Sort Key: blog_id, end_date

                    ->  Append  (cost=204.94..13440.05 rows=2082 width=467)

                          ->  Nested Loop  (cost=204.94..1937.22 rows=213 width=467)

                                ->  Hash Join  (cost=204.94..832.04 rows=213 width=467)

                                      Hash Cond: (ob2._product_association.id = _product_billing_tmp.parent_id)

                                      ->  Seq Scan on _product_association  (cost=0.00..607.50 rows=932 width=16)

                                            Filter: (product_id = 6)

                                      ->  Hash  (cost=187.66..187.66 rows=1382 width=467)

                                            ->  Seq Scan on _product_billing_tmp  (cost=0.00..187.66 rows=1382 width=467)

                                                  Filter: ((path <@ '0.1'::ltree) AND (state = 1))

                                ->  Index Scan using _blog_pkey on _blog  (cost=0.00..5.17 rows=1 width=8)

                                      Index Cond: (ob2._blog.id = ob2._product_association.parent_id)

                          ->  Nested Loop  (cost=619.15..6032.24 rows=938 width=393)

                                ->  Hash Join  (cost=619.15..1165.29 rows=938 width=393)

                                      Hash Cond: (_product_billing.parent_id = ob2._product_association.id)

                                      ->  Seq Scan on _product_billing  (cost=0.00..513.98 rows=6077 width=393)

                                            Filter: (path <@ '0.1'::ltree)

                                      ->  Hash  (cost=607.50..607.50 rows=932 width=16)

                                            ->  Seq Scan on _product_association  (cost=0.00..607.50 rows=932 width=16)

                                                  Filter: (product_id = 6)

                                ->  Index Scan using _blog_pkey on _blog  (cost=0.00..5.17 rows=1 width=8)

                                      Index Cond: (ob2._blog.id = ob2._product_association.parent_id)

                          ->  Nested Loop  (cost=0.00..5449.77 rows=931 width=16)

                                ->  Seq Scan on _product_association  (cost=0.00..622.60 rows=931 width=16)

                                      Filter: ((path <@ '0.1'::ltree) AND (product_id = 6))

                                ->  Index Scan using _blog_pkey on _blog  (cost=0.00..5.17 rows=1 width=8)

                                      Index Cond: (ob2._blog.id = ob2._product_association.parent_id)

  ->  Index Scan using stats_day_blog_blogdate on stats_day_blog  (cost=0.00..326.27 rows=116 width=16)

        Index Cond: ((stats_day_blog.idblog = all_end_date.blog_id) AND (stats_day_blog."day" > all_end_date.end_date))

33 ligne(s)

Temps d'exécution total : 38.792 ms

Requête SQL exécutée.