From: Richard Huxton <dev@archonet.com>
To: Martin Nickel <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential scan on FK join
Date: Mon, 17 Oct 2005 09:45:00 +0100
Message-ID: <4353648C.8040507@archonet.com> (raw)
In-Reply-To: <pan.2005.10.12.20.40.22.703085@portant.com>
References: <pan.2005.10.12.20.40.22.703085@portant.com>
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
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-performance@postgresql.org
Cc: dev@archonet.com, martin@portant.com
Subject: Re: Sequential scan on FK join
In-Reply-To: <4353648C.8040507@archonet.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