Re: [NEWBIE] need help optimizing this query

On Wed, 2004-03-10 at 23:08, Stephan Szabo wrote:
> On Wed, 10 Mar 2004, Dexter Tad-y wrote:
>
> > Greetings,
> > I need help in optimizing this query:
> >
> > select a.id, b.transaction from test as a left join pg_locks as b on
> > a.xmax = b.transaction where b.transaction is null;
> >
> > im using the query in obtaining records not locked by other
> > transactions. any help is appreciated.
>
> It's hard to say without knowing more about the size of a and explain
> analyze output.  On my 7.4 machine, using NOT IN rather than the left join
> gives about a 2x speed increase on a 400k row table.


here's what comes up with explain:

1) using LEFT JOIN

csp=> explain select a.id, b.transaction from test as a left join
pg_locks as b  on a.xmax = b.transaction having transaction is null;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Hash Left Join  (cost=15.00..340.01 rows=1000 width=12)
   Hash Cond: ("outer".xmax = "inner"."transaction")
   Filter: ("inner"."transaction" IS NULL)
   ->  Seq Scan on test a  (cost=0.00..20.00 rows=1000 width=12)
   ->  Hash  (cost=12.50..12.50 rows=1000 width=4)
         ->  Function Scan on pg_lock_status l  (cost=0.00..12.50
rows=1000 width=4)
(6 rows)



2) using NOT IN

csp=> explain select * from test where id not in (select test.id from
test, pg_locks where pg_locks.transaction=test.xmax);
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=352.51..375.01 rows=500 width=32)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Hash Join  (cost=15.00..340.01 rows=5001 width=8)
           Hash Cond: ("outer".xmax = "inner"."transaction")
           ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 width=12)
           ->  Hash  (cost=12.50..12.50 rows=1000 width=4)
                 ->  Function Scan on pg_lock_status l
(cost=0.00..12.50 rows=1000 width=4)
(8 rows)


which of the two is faster? :D

cheers!


Dexter Tad-y