Date: Thu, 15 Dec 2005 13:36:00 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Simple Join
In-reply-to: <200512141752.45866.blargity@gmail.com>
To: Kevin Brown <blargity@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-id: <43A0BA70.8000802@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>
	<43A0AB0A.3000403@paradise.net.nz>
	<200512141752.45866.blargity@gmail.com>

Kevin Brown wrote:

> 
> 
> I only had two explicit indexes.  One was on to_ship.ordered_product_id and 
> the other was on ordered_products.paid.  ordered_products.id is a primary 
> key.  This is on your query with an index added on suspended_sub:
> 
> dli=# explain analyze SELECT ordered_products.product_id
> dli-# FROM to_ship INNER JOIN ordered_products
> dli-# ON (to_ship.ordered_product_id = ordered_products.id
> dli(#      AND ordered_products.paid = TRUE      AND 
> dli(# ordered_products.suspended_sub = FALSE);
>                                                                QUERY PLAN                                                                        
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=5126.19..31528.40 rows=20591 width=8) (actual 
> time=4554.190..23519.618 rows=14367 loops=1)
>    Hash Cond: ("outer".ordered_product_id = "inner".id)
>    ->  Seq Scan on to_ship  (cost=0.00..11529.12 rows=611612 width=8) (actual 
> time=11.254..15192.042 rows=611612 loops=1)
>    ->  Hash  (cost=4954.79..4954.79 rows=21759 width=16) (actual 
> time=4494.900..4494.900 rows=18042 loops=1)
>          ->  Index Scan using paid_index on ordered_products  
> (cost=0.00..4954.79 rows=21759 width=16) (actual time=72.431..4414.697 
> rows=18042 loops=1)
>                Index Cond: (paid = true)
>                Filter: (paid AND (NOT suspended_sub))
>  Total runtime: 23532.785 ms
> (8 rows)
> 

Well - that had no effect at all :-) You don't have and index on 
to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and 
let use know what happens (you may want to play with SET 
enable_seqscan=off as well).

And also, if you are only ever interested in paid = true and 
suspended_sub = false, then you can recreate these indexes as partials - 
e.g:

CREATE INDEX paid_index ON ordered_products (paid) WHERE paid = true;
CREATE INDEX suspended_sub_index ON ordered_products (suspended_sub) 
WHERE suspended_sub = false;

> So what's the best way to performance wiggle this info out of the db?  The 
> list of values is only about 30 tuples long out of this query, so I was 
> figuring I could trigger on insert to to_ship to place the value into another 
> table if it didn't already exist.  I'd rather the writing be slow than the 
> reading.

Yeah - all sort of horrible denormalizations are possible :-), hopefully 
we can get the original query to work ok, and avoid the need to add code 
or triggers to you app.