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