Message-ID: <4353648C.8040507@archonet.com>
Date: Mon, 17 Oct 2005 09:45:00 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051010)
MIME-Version: 1.0
To: Martin Nickel <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential scan on FK join
References: <pan.2005.10.12.20.40.22.703085@portant.com>
In-Reply-To: <pan.2005.10.12.20.40.22.703085@portant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Martin Nickel wrote:
> EXPLAIN SELECT m.mailcode, l.lead_id
>   FROM mailing m 
>  INNER JOIN lead l ON m.mailing_id = l.mailing_id 
>  WHERE (m.maildate >= '2005-7-01'::date 
>          AND m.maildate < '2005-8-01'::date) 
> 
> Hash Join  (cost=62.13..2001702.55 rows=2711552 width=20)
>   Hash Cond: ("outer".mailing_id = "inner".mailing_id)
>   ->  Seq Scan on lead l  (cost=0.00..1804198.60 rows=34065260 width=8)
>   ->  Hash  (cost=61.22..61.22 rows=362 width=20)
>         ->  Index Scan using mailing_maildate_idx on mailing m  (cost=0.00..61.22 rows=362 width=20)
>               Index Cond: ((maildate >= '2005-07-01'::date) AND (maildate < '2005-08-01'::date))

Well the reason *why* is that the planner expects 2.71 million rows to 
be matched. If that was the case, then a seq-scan of 34 million rows 
might well make sense. The output from EXPLAIN ANALYSE would show us 
whether that estimate is correct - is it?

-- 
   Richard Huxton
   Archonet Ltd