Postgresql compare 2 querys for optimization

Question

I just created a couple of queries that bring the same data but in a different. the first one uses a sub query and the second one uses a self join strategy. checking the documentation, i found the ANALYZE and EXPLAIN commands, Now i'm trying to understand which query is better. this is the result of EXPLAIN ANALYZE for each query. Hope some one can give me some explanation about the result and if it is possible give me some point of reference where to find more information, tank you.

 EXPLAIN ANALYZE
 SELECT historicoestatusrequisicion_id, requisicion_id, estatusrequisicion_id, 
       comentario, fecha_estatus, usuario_id
  FROM historicoestatusrequisicion
  WHERE requisicion_id IN
  (
  SELECT requisicion_id FROM historicoestatusrequisicion
  WHERE usuario_id = 27 AND estatusrequisicion_id = 1
  )
ORDER BY requisicion_id, estatusrequisicion_id;

this is the result

"Sort  (cost=240.15..242.42 rows=906 width=58) (actual time=72.470..80.575 rows=3066 loops=1)"
"  Sort Key: public.historicoestatusrequisicion.requisicion_id, public.historicoestatusrequisicion.estatusrequisicion_id"
"  Sort Method:  quicksort  Memory: 436kB"
"  ->  Hash Join  (cost=96.44..195.65 rows=906 width=58) (actual time=16.198..46.765 rows=3066 loops=1)"
"        Hash Cond: (public.historicoestatusrequisicion.requisicion_id = public.historicoestatusrequisicion.requisicion_id)"
"        ->  Seq Scan on historicoestatusrequisicion  (cost=0.00..78.66 rows=3066 width=58) (actual time=0.018..8.616 rows=3066 loops=1)"
"        ->  Hash  (cost=95.45..95.45 rows=79 width=7) (actual time=16.132..16.132 rows=904 loops=1)"
"              ->  HashAggregate  (cost=94.66..95.45 rows=79 width=7) (actual time=10.475..13.109 rows=904 loops=1)"
"                    ->  Seq Scan on historicoestatusrequisicion  (cost=0.00..93.99 rows=267 width=7) (actual time=1.309..5.329 rows=904 loops=1)"
"                          Filter: ((usuario_id = 27) AND (estatusrequisicion_id = 1))"
"Total runtime: 88.682 ms"

Second query

  EXPLAIN ANALYZE
  SELECT hist1.historicoestatusrequisicion_id, hist1.requisicion_id, hist1.estatusrequisicion_id, hist1.comentario, hist1.fecha_estatus, hist1.usuario_id
  FROM historicoestatusrequisicion hist1
  JOIN historicoestatusrequisicion hist2 ON hist2.requisicion_id = hist1.requisicion_id
  WHERE hist2.usuario_id = 27 AND hist2.estatusrequisicion_id = 1
  ORDER BY hist1.requisicion_id, hist1.estatusrequisicion_id;

This is the result

"Sort  (cost=248.71..250.97 rows=906 width=58) (actual time=34.833..40.601 rows=3066 loops=1)"
"  Sort Key: hist1.requisicion_id, hist1.estatusrequisicion_id"
"  Sort Method:  quicksort  Memory: 436kB"
"  ->  Hash Join  (cost=97.33..204.21 rows=906 width=58) (actual time=4.320..23.515 rows=3066 loops=1)"
"        Hash Cond: (hist1.requisicion_id = hist2.requisicion_id)"
"        ->  Seq Scan on historicoestatusrequisicion hist1  (cost=0.00..78.66 rows=3066 width=58) (actual time=0.010..5.886 rows=3066 loops=1)"
"        ->  Hash  (cost=93.99..93.99 rows=267 width=7) (actual time=4.289..4.289 rows=904 loops=1)"
"              ->  Seq Scan on historicoestatusrequisicion hist2  (cost=0.00..93.99 rows=267 width=7) (actual time=0.425..2.316 rows=904 loops=1)"
"                    Filter: ((usuario_id = 27) AND (estatusrequisicion_id = 1))"
"Total runtime: 46.387 ms"

Answer 1

The analyzer is telling you what the actual execution path was. For the second query

It sorted all of the rows based off of the estatusrequisicion ID, it used quicksort to achieve this. The time it took was 34 ms.

It next joined that result set on the hist1.requisicion_id = hist2.requisicion_id. Since these are key's it was able to do a hash look up.
A Hash is how the primary/foreign keys are stored. The field is computed into a hash
and the lookup speed is 1.

Next it joined that result set on historicestatusrequisicion using another primary/foreign key.

The costs (they are estimates) are in ms, actual time is how long (actual took)it took

decent entry level explanation of execution plans

Answer 2

I suggest you watch the EnterpriseDB webcasts on Performance Tuning.

Signup is free, the files are huge, but worth it.