From: | Martin Nickel <martin(at)portant(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Sequential scan on FK join |
Date: | 2005-10-12 20:40:24 |
Message-ID: | pan.2005.10.12.20.40.22.703085@portant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
All,
I can see why the query below is slow. The lead table is 34 million rows,
and a sequential scan always takes 3+ minutes. Mailing_id is the PK for
mailing and is constrained as a foreign key (NULLS allowed) in lead.
There is an index on lead.mailing_id. I've just run VACUUM ANALYZE on
lead. I don't understand why it isn't being used.
Thanks for your help,
Martin Nickel
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)
-- takes 510,145 ms
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))
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2005-10-12 22:00:36 | Re: Help tuning postgres |
Previous Message | Merlin Moncure | 2005-10-12 18:17:47 | Re: Help tuning postgres |