Message-ID: <4354A9AF.30300@archonet.com>
Date: Tue, 18 Oct 2005 08:52:15 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
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>
	<4353648C.8040507@archonet.com>
	<pan.2005.10.17.13.07.53.915839@portant.com>
	<4353E342.2060708@archonet.com>
	<pan.2005.10.17.19.56.36.629275@portant.com>
In-Reply-To: <pan.2005.10.17.19.56.36.629275@portant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Martin Nickel wrote:
> When I turn of seqscan it does use the index - and it runs 20 to 30%
> longer.  Based on that, the planner is correctly choosing a sequential
> scan - but that's just hard for me to comprehend.  I'm joining on an int4
> key, 2048 per index page - I guess that's a lot of reads - then the data
> -page reads.  Still, the 8-minute query time seems excessive.  

You'll be getting (many) fewer than 2048 index entries per page. There's 
a page header and various pointers involved too, and index pages aren't 
going to be full. So - it needs to search the table on dates, fetch the 
id's and then assemble them for the hash join. Of course, if you have 
too many to join then all this will spill to disk slowing you further.

Now, you'd rather get down below 8 minutes. There are a number of options:
  1. Make sure your disk i/o is being pushed to its limit
  2. Look into increasing the sort memory for this one query "set 
work_mem..." (see the runtime configuration section of the manual)
  3. Actually - are you happy that your general configuration is OK?
  4. Perhaps use a cursor - I'm guessing you want to process these 
mailings in some way and only want them one at a time in any case.
  5. Try the query one day at a time and see if the balance tips the 
other way - you'll be dealing with substantially less data per query 
which might match your system better. Of course, this may not be 
practical for your applicaton.
  6. If your lead table is updated only rarely, you could try a CLUSTER 
on the table by mailing_id - that should speed the scan. Read the manual 
for the cluster command first though.

-- 
   Richard Huxton
   Archonet Ltd