Date: Thu, 15 Dec 2005 12:30:18 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Simple Join
In-reply-to: <200512141603.52583.blargity@gmail.com>
To: Kevin Brown <blargity@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-id: <43A0AB0A.3000403@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <200512141603.52583.blargity@gmail.com>

Kevin Brown wrote:
> I'll just start by warning that I'm new-ish to postgresql.
> 
> I'm running 8.1 installed from source on a Debian Sarge server.  I have a 
> simple query that I believe I've placed the indexes correctly for, and I 
> still end up with a seq scan.  It makes sense, kinda, but it should be able 
> to use the index to gather the right values.  I do have a production set of 
> data inserted into the tables, so this is running realistically:
> 
> dli=# explain analyze SELECT ordered_products.product_id
> dli-# FROM to_ship, ordered_products
> dli-# WHERE to_ship.ordered_product_id = ordered_products.id AND
> dli-# ordered_products.paid = TRUE AND
> dli-# ordered_products.suspended_sub = FALSE;

You scan 600000 rows from to_ship to get about 25000 - so some way to 
cut this down would help.

Try out an explicit INNER JOIN which includes the filter info for paid 
and suspended_sub in the join condition (you may need indexes on each of 
id, paid and suspended_sub, so that the 8.1 optimizer can use a bitmap 
scan):


SELECT ordered_products.product_id
FROM to_ship INNER JOIN ordered_products
ON (to_ship.ordered_product_id = ordered_products.id
     AND ordered_products.paid = TRUE      AND 
ordered_products.suspended_sub = FALSE);