pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: John Lister <john.lister-ps@kickstone.com>
To: pgsql-sql@postgresql.org
Subject: Query planning question
Date: Mon, 11 May 2009 15:43:16 +0100
Message-ID: <CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com> (raw )
Doing the following query
select distinct m.id, m.name
from manufacturer_manufacturer m
join product_product p on (p.manufacturer_id=m.id)
join retailer_offer o on (o.product_id=p.id)
where o.retailer_id=XXX and o.active
results in one of 2 query plans depending upon the value of XXX.
The first ignores the index on products and does a hash join which is very slow, the second uses the index and does a nested loop which is fast.
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?
"Unique (cost=318308.62..321110.94 rows=1029 width=13) (actual time=5057.271..5296.973 rows=699 loops=1)"
" -> Sort (cost=318308.62..319242.73 rows=373642 width=13) (actual time=5057.270..5196.780 rows=455733 loops=1)"
" Sort Key: m.id, m.name"
" Sort Method: external merge Disk: 11032kB"
" -> Hash Join (cost=110196.74..283725.63 rows=373642 width=13) (actual time=1706.287..3451.352 rows=455733 loops=1)"
" Hash Cond: (p.manufacturer_id = m.id)"
" -> Hash Join (cost=110163.59..278554.90 rows=373642 width=4) (actual time=1705.652..3230.879 rows=455733 loops=1)"
" Hash Cond: (o.product_id = p.id)"
" -> Bitmap Heap Scan on retailer_offer o (cost=9418.68..157960.21 rows=373642 width=4) (actual time=120.277..382.208 rows=455733 loops=1)"
" Recheck Cond: ((retailer_id = 1347) AND active)"
" -> Bitmap Index Scan on idx_retaileroffer_retailerid (cost=0.00..9325.27 rows=373642 width=0) (actual time=79.503..79.503 rows=455829 loops=1)"
" Index Cond: (retailer_id = 1347)"
" -> Hash (cost=59067.07..59067.07 rows=2540307 width=8) (actual time=1584.994..1584.994 rows=2540324 loops=1)"
" -> Seq Scan on product_product p (cost=0.00..59067.07 rows=2540307 width=8) (actual time=0.008..698.313 rows=2540324 loops=1)"
" -> 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.007..0.278 rows=1029 loops=1)"
"Total runtime: 5310.663 ms"
"Unique (cost=43237.52..43266.80 rows=1029 width=13) (actual time=190.978..196.625 rows=276 loops=1)"
" -> Sort (cost=43237.52..43247.28 rows=3903 width=13) (actual time=190.977..192.431 rows=11298 loops=1)"
" Sort Key: m.id, m.name"
" Sort Method: quicksort Memory: 1037kB"
" -> Hash Join (cost=134.14..43004.70 rows=3903 width=13) (actual time=5.006..155.188 rows=11298 loops=1)"
" Hash Cond: (p.manufacturer_id = m.id)"
" -> Nested Loop (cost=100.99..42917.88 rows=3903 width=4) (actual time=4.363..146.421 rows=11298 loops=1)"
" -> Bitmap Heap Scan on retailer_offer o (cost=100.99..13663.63 rows=3903 width=4) (actual time=4.345..29.871 rows=11298 loops=1)"
" Recheck Cond: ((retailer_id = 1710) AND active)"
" -> Bitmap Index Scan on idx_retaileroffer_retailerid (cost=0.00..100.02 rows=3903 width=0) (actual time=2.368..2.368 rows=11380 loops=1)"
" Index Cond: (retailer_id = 1710)"
" -> Index Scan using product_product_pkey on product_product p (cost=0.00..7.48 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=11298)"
" Index Cond: (p.id = o.product_id)"
" -> Hash (cost=20.29..20.29 rows=1029 width=13) (actual time=0.634..0.634 rows=1029 loops=1)"
" -> Seq Scan on manufacturer_manufacturer m (cost=0.00..20.29 rows=1029 width=13) (actual time=0.009..0.275 rows=1029 loops=1)"
"Total runtime: 196.716 ms"
Thanks
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
view thread (3+ messages) latest in thread
Message-ID: <CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com>
Permalink: ../CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com/
Also on: postgresql.org/message-id/CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: john.lister-ps@kickstone.com
Subject: Re: Query planning question
In-Reply-To: <CDFB6E649FAB42D69FC5D4456A0F44E1@squarepi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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