Re: [NEWBIE] need help optimizing this query
От | Dexter Tad-y |
---|---|
Тема | Re: [NEWBIE] need help optimizing this query |
Дата | |
Msg-id | 1078932892.2833.53.camel@localhost обсуждение исходный текст |
Ответ на | Re: [NEWBIE] need help optimizing this query (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Ответы |
Re: [NEWBIE] need help optimizing this query
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: