Skip site navigation (1) Skip section navigation (2)

Re: Redundant sub query triggers slow nested loop left join

From: "henk de wit" <henk53602(at)hotmail(dot)com>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Redundant sub query triggers slow nested loop left join
Date: 2007-04-23 11:35:26
Message-ID: BAY106-F32678905DA322D0731EA6FF54B0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance
>Well, that's darn odd.  It should not be getting that so far wrong.
>What's the datatype of the status column exactly (I'm guessing varchar
>but maybe not)?  Would you show us the pg_stats row for the status column?

It has been created as a char(1) in fact. The pg_stats row for the status 
column is:

public|banners_links|status|0|5|2|{0,1}|{0.626397,0.373603}||0.560611

>I'm not sure why, but I think it must have something to do
>with the subquery structure of your query.  Were you showing us the
>whole truth about your query, or were there details you left out?

The query I gave in the opening post was just a small part, the part that I 
initially identified as the 'slow path'. The last plan I gave was from the 
whole query, without any details left out. I didn't gave the SQL of that 
yet, so here it is:

SELECT
	id,
	status,
	merchant_id,
	description,
	org_text,
	users_banners_id,
	banner_url,
	cookie_redirect,
	type,

	CASE WHEN special_deal IS null THEN
		''
	ELSE
		'special deal'
	END AS special_deal,

	CASE WHEN url_of_banner IS null	THEN
		''
	ELSE
		url_of_banner
	END AS url_of_banner,

	CASE WHEN period_end IS NULL THEN
		'not_active'
	ELSE
		'active'
	END AS active_not_active,

	CASE WHEN ecpc IS NULL THEN
		0.00
	ELSE
		ROUND(ecpc::numeric,2)
	END AS ecpc,

	CASE WHEN ecpc_merchant IS NULL THEN
		0.00
	ELSE
		ROUND(ecpc_merchant::numeric,2)
	END AS ecpc_merchant

FROM
	/* SUBQUERY grand_total_fetch_banners */ (
		/* SUBQUERY grand_total  */(
			/* SUBQUERY banners_special_deals */	(

				/* SUBQUERY banners */ (
					SELECT
						*
					FROM
						/* SUBQUERY banners_links */ (
							SELECT
								banners_links.id,
								merchant_id,
								banners_org.banner_text AS org_text,
								description,
								status,
								banner_url,
								ecpc,
								ecpc_merchant,
								COALESCE(cookie_redirect,0) AS cookie_redirect
							FROM
								/* SUBQUERY banners_links  */ (

									/* subselect tot join ecpc_per_banner_links on banners_links*/
									/* SUBQUERY banners_links */ (
										SELECT
											*
										FROM
											banners_links
										WHERE
											merchant_id = 5631
									) AS banners_links

										LEFT OUTER JOIN

									/* SUBQUERY ecpc_per_banner_link */	(
										SELECT
											CASE WHEN clicks_total > 0 THEN
												(revenue_total_affiliate/clicks_total)::float/1000.0
											ELSE
												0.0
											END AS ecpc,
											CASE WHEN clicks_total > 0 THEN
												(revenue_total/clicks_total)::float/1000.0
											ELSE
												0.0
											END AS ecpc_merchant,
											banners_links_id
										FROM
											precalculated_stats_banners_links
										WHERE
											status = 4			AND
											banners_links_id IN /* SUBQUERY */ (
												SELECT
													id
												FROM
													banners_links
												WHERE
													merchant_id = 5631
											)
										ORDER BY
											ecpc DESC
									) AS ecpc_per_banner_link

										ON (banners_links.id = ecpc_per_banner_link.banners_links_id)
								) AS banners_links

									,

								banners_org

							WHERE
								merchant_id = 5631							AND
								banners_links.id = banners_org.id_banner			AND
								(banners_links.id = -1 OR -1 = -1)	AND
								(banners_links.status = 0 OR 0 = -1)
						) AS banners_links

							LEFT OUTER JOIN

						/* SUBQUERY users_banners_tot_sub */(
							SELECT
								MAX (users_banners_id) AS users_banners_id,
								merchant_users_banners_id,
								banner_id
							FROM
								/* SUBQUERY users_banners_rotations_sub */(
									SELECT
										affiliate_id 		AS merchant_users_banners_id,
										users_banners.id 	AS users_banners_id,
										users_banners_rotation.banner_id
									FROM
										users_banners, users_banners_rotation
									WHERE
										affiliate_id = 5631									AND
										users_banners_rotation.users_banners_id = users_banners.id	AND
										users_banners.status = 3
								) AS users_banners_rotations_sub
							GROUP BY
								merchant_users_banners_id,banner_id
						) AS users_banners_tot_sub

							ON (
								banners_links.id = users_banners_tot_sub.banner_id 	AND
								banners_links.merchant_id = 
users_banners_tot_sub.merchant_users_banners_id
							)
					) AS banners

						LEFT OUTER JOIN

					/* SUBQUERY special_deals */(
						SELECT
							banner_deals.banner_id 	AS id,
							MAX(affiliate_id) 		AS special_deal
						FROM
							banner_deals
						GROUP BY
							banner_deals.banner_id
					) AS special_deals

						USING (id)

			) AS banners_special_deals

				LEFT OUTER JOIN

			/* SUBQUERY types */ (
				SELECT
					banner_types.id 				AS type_id,
					banner_types.type 				AS type,
					banners_banner_types.banner_id 	AS id
				FROM
					banner_types,banners_banner_types
				WHERE
					banners_banner_types.banner_id IN /* SUBQUERY */ (
						SELECT
							id
						FROM
							banners_links
						WHERE
							merchant_id = 5631
					) AND
					banners_banner_types.type_id = banner_types.id
		    ) AS types

				USING (id)

		)  as grand_total

			LEFT OUTER JOIN

		/* SUBQUERY fetch_banners */ (
			SELECT
				banners_links_id AS id,
				url_of_banner
			FROM
				fetch_banners
		) AS fetch_banners

			USING (id)
	) AS grand_total_fetch_banners

		LEFT OUTER JOIN

    /* SUBQUERY active_banners */ (
    	SELECT
	    	banner_id AS id,
	    	period_end
    	FROM
    		reward_ratings
    	WHERE
    		now() BETWEEN period_start AND period_end
    	AND
    		banner_id IN /* SUBQUERY */ (
    			SELECT
    				id
    			FROM
    				banners_links
    			WHERE
    				merchant_id = 5631
    		)
    ) AS active_banners

    	USING (id)

WHERE
	(type_id =  -1 OR -1 = -1 )	AND
	(special_deal IS null)

ORDER BY
	id DESC

This is the original query without even the earlier mentioned redundant 
check removed. For this query, PG 8.2 creates the following plan:

Sort  (cost=5094.40..5094.41 rows=1 width=597) (actual 
time=15282.503..15282.734 rows=553 loops=1)
  Sort Key: public.banners_links.id
  ->  Nested Loop Left Join  (cost=3883.68..5094.39 rows=1 width=597) 
(actual time=64.066..15280.773 rows=553 loops=1)
        Join Filter: (public.banners_links.id = reward_ratings.banner_id)
        ->  Nested Loop Left Join  (cost=2926.37..3486.98 rows=1 width=589) 
(actual time=51.992..9231.245 rows=553 loops=1)
              Join Filter: (public.banners_links.id = 
public.fetch_banners.banners_links_id)
              ->  Nested Loop Left Join  (cost=2926.37..3483.00 rows=1 
width=519) (actual time=51.898..9183.007 rows=553 loops=1)
                    Join Filter: (public.banners_links.id = 
ecpc_per_banner_link.banners_links_id)
                    ->  Nested Loop  (cost=1050.35..1602.14 rows=1 
width=503) (actual time=29.585..9015.077 rows=553 loops=1)
                          ->  Nested Loop Left Join  (cost=1050.35..1593.86 
rows=1 width=124) (actual time=29.577..9010.273 rows=553 loops=1)
                                Join Filter: (public.banners_links.id = 
users_banners_tot_sub.banner_id)
                                ->  Nested Loop Left Join  
(cost=1033.74..1577.21 rows=1 width=116) (actual time=25.904..8738.006 
rows=553 loops=1)
                                      Join Filter: (public.banners_links.id 
= special_deals.id)
                                      Filter: (special_deals.special_deal IS 
NULL)
                                      ->  Nested Loop Left Join  
(cost=964.12..1480.67 rows=1 width=108) (actual time=20.905..8259.497 
rows=553 loops=1)
                                            Join Filter: 
(public.banners_links.id = banners_banner_types.banner_id)
                                            ->  Bitmap Heap Scan on 
banners_links  (cost=4.35..42.12 rows=1 width=73) (actual time=0.160..1.122 
rows=359 loops=1)
                                                  Recheck Cond: 
((merchant_id = 5631) AND (merchant_id = 5631))
                                                  Filter: ((status)::text = 
'0'::text)
                                                  ->  Bitmap Index Scan on 
banners_links_merchant_id_idx  (cost=0.00..4.35 rows=10 width=0) (actual 
time=0.123..0.123 rows=424 loops=1)
                                                        Index Cond: 
((merchant_id = 5631) AND (merchant_id = 5631))
                                            ->  Hash Join  
(cost=959.77..1432.13 rows=514 width=43) (actual time=0.899..22.685 rows=658 
loops=359)
                                                  Hash Cond: 
(banners_banner_types.type_id = banner_types.id)
                                                  ->  Hash IN Join  
(cost=957.32..1422.52 rows=540 width=16) (actual time=0.897..21.946 rows=658 
loops=359)
                                                        Hash Cond: 
(banners_banner_types.banner_id = public.banners_links.id)
                                                        ->  Seq Scan on 
banners_banner_types  (cost=0.00..376.40 rows=22240 width=16) (actual 
time=0.004..10.164 rows=22240 loops=359)
                                                        ->  Hash  
(cost=952.02..952.02 rows=424 width=8) (actual time=0.790..0.790 rows=424 
loops=1)
                                                              ->  Bitmap 
Heap Scan on banners_links  (cost=11.54..952.02 rows=424 width=8) (actual 
time=0.108..0.503 rows=424 loops=1)
                                                                    Recheck 
Cond: (merchant_id = 5631)
                                                                    ->  
Bitmap Index Scan on banners_links_merchant_id_idx  (cost=0.00..11.43 
rows=424 width=0) (actual time=0.078..0.078 rows=424 loops=1)
                                                                          
Index Cond: (merchant_id = 5631)
                                                  ->  Hash  (cost=2.20..2.20 
rows=20 width=43) (actual time=0.033..0.033 rows=20 loops=1)
                                                        ->  Seq Scan on 
banner_types  (cost=0.00..2.20 rows=20 width=43) (actual time=0.004..0.017 
rows=20 loops=1)
                                      ->  HashAggregate  (cost=69.62..79.24 
rows=769 width=16) (actual time=0.008..0.498 rows=780 loops=553)
                                            ->  Seq Scan on banner_deals  
(cost=0.00..53.75 rows=3175 width=16) (actual time=0.004..1.454 rows=3175 
loops=1)
                                ->  HashAggregate  (cost=16.61..16.62 rows=1 
width=24) (actual time=0.007..0.291 rows=424 loops=553)
                                      ->  Nested Loop  (cost=0.00..16.60 
rows=1 width=24) (actual time=0.056..3.123 rows=424 loops=1)
                                            ->  Index Scan using 
users_banners_affiliate_id_idx on users_banners  (cost=0.00..8.30 rows=1 
width=16) (actual time=0.046..0.555 rows=424 loops=1)
                                                  Index Cond: ((affiliate_id 
= 5631) AND (affiliate_id = 5631))
                                                  Filter: ((status)::text = 
'3'::text)
                                            ->  Index Scan using 
users_banners_id_idx on users_banners_rotation  (cost=0.00..8.29 rows=1 
width=16) (actual time=0.003..0.004 rows=1 loops=424)
                                                  Index Cond: 
(users_banners_rotation.users_banners_id = users_banners.id)
                          ->  Index Scan using "banners_org_id_banner.idx" 
on banners_org  (cost=0.00..8.27 rows=1 width=387) (actual time=0.005..0.006 
rows=1 loops=553)
                                Index Cond: (public.banners_links.id = 
banners_org.id_banner)
                    ->  Sort  (cost=1876.01..1876.50 rows=194 width=30) 
(actual time=0.041..0.161 rows=290 loops=553)
                          Sort Key: CASE WHEN 
(precalculated_stats_banners_links.clicks_total > 0) THEN 
(((precalculated_stats_banners_links.revenue_total_affiliate / 
(precalculated_stats_banners_links.clicks_total)::numeric))::double 
precision / 1000::double precision) ELSE 0::double precision END
                          ->  Merge IN Join  (cost=1819.78..1868.64 rows=194 
width=30) (actual time=16.769..21.879 rows=290 loops=1)
                                Merge Cond: 
(precalculated_stats_banners_links.banners_links_id = 
public.banners_links.id)
                                ->  Sort  (cost=849.26..869.24 rows=7993 
width=30) (actual time=12.486..15.740 rows=7923 loops=1)
                                      Sort Key: 
precalculated_stats_banners_links.banners_links_id
                                      ->  Index Scan using 
pre_calc_banners_status on precalculated_stats_banners_links  
(cost=0.00..331.13 rows=7993 width=30) (actual time=0.007..6.291 rows=7923 
loops=1)
                                            Index Cond: (status = 4)
                                ->  Sort  (cost=970.52..971.58 rows=424 
width=8) (actual time=0.879..1.023 rows=366 loops=1)
                                      Sort Key: public.banners_links.id
                                      ->  Bitmap Heap Scan on banners_links  
(cost=11.54..952.02 rows=424 width=8) (actual time=0.123..0.509 rows=424 
loops=1)
                                            Recheck Cond: (merchant_id = 
5631)
                                            ->  Bitmap Index Scan on 
banners_links_merchant_id_idx  (cost=0.00..11.43 rows=424 width=0) (actual 
time=0.089..0.089 rows=424 loops=1)
                                                  Index Cond: (merchant_id = 
5631)
              ->  Seq Scan on fetch_banners  (cost=0.00..2.88 rows=88 
width=78) (actual time=0.003..0.042 rows=88 loops=553)
        ->  Hash IN Join  (cost=957.32..1606.24 rows=93 width=16) (actual 
time=10.933..10.933 rows=0 loops=553)
              Hash Cond: (reward_ratings.banner_id = 
public.banners_links.id)
              ->  Seq Scan on reward_ratings  (cost=0.00..633.66 rows=3822 
width=16) (actual time=0.007..8.955 rows=4067 loops=553)
                    Filter: ((now() >= period_start) AND (now() <= 
period_end))
              ->  Hash  (cost=952.02..952.02 rows=424 width=8) (actual 
time=0.738..0.738 rows=424 loops=1)
                    ->  Bitmap Heap Scan on banners_links  
(cost=11.54..952.02 rows=424 width=8) (actual time=0.118..0.475 rows=424 
loops=1)
                          Recheck Cond: (merchant_id = 5631)
                          ->  Bitmap Index Scan on 
banners_links_merchant_id_idx  (cost=0.00..11.43 rows=424 width=0) (actual 
time=0.087..0.087 rows=424 loops=1)
                                Index Cond: (merchant_id = 5631)
Total runtime: 15283.225 ms

If I change 1 of the redundant checks:

/* SUBQUERY banners_links */ (
	SELECT
		*
	FROM
		banners_links
	WHERE
		merchant_id = 5631
) AS banners_links

into just banner_links, PG comes up with the (large) plan I posted earlier.

_________________________________________________________________
Live Search, for accurate results! http://www.live.nl


pgsql-performance by date

Next:From: Dave DutcherDate: 2007-04-23 14:56:46
Subject: Re: not using indexes on large table
Previous:From: Heikki LinnakangasDate: 2007-04-23 09:46:45
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group