On 16.3.2015 19:50, Marc Watson wrote:
> Hello all,
> I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800,
64-bit, as downloaded from EnterpriseDB, and is running on my dev system
under Win 7 64-bit.
> I hope someone can help me with a problem I'm having when joining a
view with a table. The view is somewhat involved, but I can provide the
details if necessary
> A query on the table is quick (16 ms):
<
> explain analyze select ir_actor_id from f_intervenant_ref where ir_dos_id = '5226' order by ir_actor_id;
>
> "Sort (cost=17.28..17.30 rows=8 width=4) (actual time=0.032..0.033 rows=8 loops=1)"
> " Sort Key: ir_actor_id"
> " Sort Method: quicksort Memory: 25kB"
> " -> Index Scan using ir_dos_id_idx on f_intervenant_ref (cost=0.28..17.16 rows=8 width=4) (actual
time=0.019..0.024rows=8 loops=1)"
> " Index Cond: ((ir_dos_id)::text = '5226'::text)"
> "Planning time: 0.180 ms"
> "Execution time: 0.049 ms"
> ..
ISTM the database is applying the IN() condition last, i.e. it executes
SELECT * FROM v_actor
and then proceeds to filter the result. I'd bet if you measure time for
that (SELECT * FROM v_actor) you'll get ~7 seconds.
First, get rid of the ORDER BY clauses in the subselects - it's
completely pointless, and might prevent proper optimization (e.g.
replacing the IN() with optimized joins.
I.e. try this:
SELECT * FROM v_actor
WHERE v_actor.actor_id IN (SELECT ir_actor_id FROM f_intervenant_ref
WHERE ir_dos_id = '5226');
I'd also try replacing this with EXISTS
SELECT * FROM v_actor
WHERE EXISTS (SELECT 1 FROM f_intervenant_ref
WHERE (actor_id = ir_actor_id)
AND (ir_dos_id = '5226'));
or even an explicit join
SELECT v_actor.* FROM v_actor JOIN f_intervenant_ref
ON (actor_id = ir_actor_id)
WHERE ir_dos_id = '5226');
That might behave a bit differently if there are multiple
f_intervenant_ref rows matching the actor. If that's the case, a simple
DISTINCT should fix that.
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
