Re: Slow query with join

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