Message-ID: <9FC7134939F8466BA0967DF3B3A8DFBC@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com>
	<834.1242054368@sss.pgh.pa.us>
Subject: Re: Query planning question 
Date: Mon, 11 May 2009 16:21:17 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

> "John Lister" <john.lister-ps@kickstone.com> writes:
>> Am I right in assuming the planner thinks a sequential scan is quicker 
>> than 10k index hits, would tweaking the costs fix this or would i be 
>> better updating the stats for the product_id and manufacturer_id fields?
>
> AFAICT the planner did exactly the right things here.  Your first
> example is fetching 40 times as many rows from retailer_offer as
> the second one is.  If the planner had stuck with the nestloop plan,
> it would've taken about 40x as long, and been significantly slower
> than the hash join.

Cheers for the quick reply, maybe not the best values, see the following 2 
plans with approx the same number of product rows but different results and 
times. I forgot to mention that the product table has 2.5M rows although 
this is apparent from the plans:

with hash join:

"Unique  (cost=199627.47..199900.51 rows=1029 width=13) (actual 
time=2226.358..2238.255 rows=49 loops=1)"
"  ->  Sort  (cost=199627.47..199718.48 rows=36406 width=13) (actual 
time=2226.356..2230.342 rows=37086 loops=1)"
"        Sort Key: m.name, m.id"
"        Sort Method:  quicksort  Memory: 3276kB"
"        ->  Hash Join  (cost=101700.78..196869.37 rows=36406 width=13) 
(actual time=1759.983..2193.453 rows=37086 loops=1)"
"              Hash Cond: (p.manufacturer_id = m.id)"
"              ->  Hash Join  (cost=101667.62..196335.64 rows=36406 width=4) 
(actual time=1759.338..2174.826 rows=37086 loops=1)"
"                    Hash Cond: (o.product_id = p.id)"
"                    ->  Bitmap Heap Scan on retailer_offer o 
(cost=921.66..84697.06 rows=36406 width=4) (actual time=12.168..49.759 
rows=37086 loops=1)"
"                          Recheck Cond: ((retailer_id = 5149) AND active)"
"                          ->  Bitmap Index Scan on 
idx_retaileroffer_retailerid  (cost=0.00..912.56 rows=36406 width=0) (actual 
time=7.136..7.136 rows=37089 loops=1)"
"                                Index Cond: (retailer_id = 5149)"
"                    ->  Hash  (cost=59067.54..59067.54 rows=2540354 
width=8) (actual time=1746.670..1746.670 rows=2540383 loops=1)"
"                          ->  Seq Scan on product_product p 
(cost=0.00..59067.54 rows=2540354 width=8) (actual time=0.012..787.095 
rows=2540383 loops=1)"
"              ->  Hash  (cost=20.29..20.29 rows=1029 width=13) (actual 
time=0.635..0.635 rows=1029 loops=1)"
"                    ->  Seq Scan on manufacturer_manufacturer m 
(cost=0.00..20.29 rows=1029 width=13) (actual time=0.009..0.296 rows=1029 
loops=1)"
"Total runtime: 2244.036 ms"

and without:

"Unique  (cost=43237.53..43266.80 rows=1029 width=13) (actual 
time=410.191..421.953 rows=332 loops=1)"
"  ->  Sort  (cost=43237.53..43247.29 rows=3903 width=13) (actual 
time=410.189..414.351 rows=32959 loops=1)"
"        Sort Key: m.name, m.id"
"        Sort Method:  quicksort  Memory: 3384kB"
"        ->  Hash Join  (cost=134.15..43004.71 rows=3903 width=13) (actual 
time=16.356..328.938 rows=32959 loops=1)"
"              Hash Cond: (p.manufacturer_id = m.id)"
"              ->  Nested Loop  (cost=100.99..42917.89 rows=3903 width=4) 
(actual time=15.716..308.037 rows=32959 loops=1)"
"                    ->  Bitmap Heap Scan on retailer_offer o 
(cost=100.99..13663.64 rows=3903 width=4) (actual time=15.693..67.479 
rows=32959 loops=1)"
"                          Recheck Cond: ((retailer_id = 2016) AND active)"
"                          ->  Bitmap Index Scan on 
idx_retaileroffer_retailerid  (cost=0.00..100.02 rows=3903 width=0) (actual 
time=7.863..7.863 rows=33369 loops=1)"
"                                Index Cond: (retailer_id = 2016)"
"                    ->  Index Scan using product_product_pkey on 
product_product p  (cost=0.00..7.48 rows=1 width=8) (actual 
time=0.006..0.006 rows=1 loops=32959)"
"                          Index Cond: (p.id = o.product_id)"
"              ->  Hash  (cost=20.29..20.29 rows=1029 width=13) (actual 
time=0.627..0.627 rows=1029 loops=1)"
"                    ->  Seq Scan on manufacturer_manufacturer m 
(cost=0.00..20.29 rows=1029 width=13) (actual time=0.009..0.270 rows=1029 
loops=1)"
"Total runtime: 422.058 ms"

You can see that the sequential scan is significantly slower than the index 
scan (i've tried to mitigate any caching by the OS with these results).
Postgresql 8.3.5 running on a Quad Core Xeon 2Ghz with 12Gb ram. All costs 
set to defaults, shared_buffers=4.2GB  and effective_cache=6Gb.
I thought with the later versions more shared_buffers was better, is this 
too much??

Thanks

JOHN