Re: Oddball data distribution giving me planner headaches

-----Original Message-----
From: Jeff Amiel [mailto:becauseimjeff(at)yahoo(dot)com]
Sent: Friday, December 02, 2011 3:52 PM
To: pgsql-general(at)postgresql(dot)org; David Johnston
Subject: RE: [GENERAL] Oddball data distribution giving me planner headaches

--- On Fri, 12/2/11, David Johnston <polobo(at)yahoo(dot)com> wrote:

> From: David Johnston <polobo(at)yahoo(dot)com> What kind of plan does the
> following give?
>
> EXPLAIN ANALYZE
> SELECT *
> FROM customer_rel p
> JOIN customer c ON (p.parent_customer = c.customer_id) WHERE
> c.customer_type = 'DISTRIBUTOR'

Nearly identical output

"Nested Loop (cost=0.00..29624.69 rows=931 width=97) (actual
time=0.032..1330.208 rows=1025401 loops=1)"
" -> Seq Scan on customer c (cost=0.00..25429.10 rows=136 width=71)
(actual time=0.017..212.059 rows=68 loops=1)"
" Filter: (customer_type = 'DISTRIBUTOR'::bpchar)"
" -> Index Scan using rel_parent on customer_rel p (cost=0.00..30.76
rows=7 width=26) (actual time=0.006..7.732 rows=15079 loops=68)"
" Index Cond: (parent_customer = c.customer_id)"
"Total runtime: 1544.281 ms"

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

What happens if you disable, say, nested loops and/or index scans?

David J.