i just did explain analyze and currently database is running slow coz of
the query
explain ANALYZE SELECT account.id, account.organization_id,
run.application_id, work_unit.script, work_unit.id, work_unit.start_time,
run.id, work_unit.priority FROM work_unit, run, account WHERE
work_unit.status = 3 AND work_unit.run_id = run.id AND work_unit.type != 1
AND run.status = 1 AND run.account_id = account.id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..24.84 rows=1 width=64) (actual
time=71.824..69729.467 rows=1820 loops=1)
-> Nested Loop (cost=0.00..16.56 rows=1 width=60) (actual
time=71.760..69628.874 rows=1820 loops=1)
Join Filter: (work_unit.run_id = run.id)
-> Index Scan using idx_work_unit_1_status_3_new on work_unit
(cost=0.00..8.28 rows=1 width=52) (actual time=0.067..154.364 rows=1820
loops=1)
Index Cond: (status = 3)
-> Index Scan using run_status_1 on run (cost=0.00..8.27 rows=1
width=12) (actual time=0.081..34.338 rows=3138 loops=1820)
Index Cond: (run.status = 1)
-> Index Scan using account_pkey on account (cost=0.00..8.27 rows=1
width=8) (actual time=0.044..0.046 rows=1 loops=1820)
Index Cond: (account.id = run.account_id)
Total runtime: 69732.893 ms
On Thu, Jul 17, 2014 at 4:45 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 7/17/2014 4:26 PM, Prabhjot Sheena wrote:
>
> Here is the explain plan. There query time went backup up to 2 to 3
> minutes from 3 second in just 2 hrs. Can anyone suggest something on how
> to fix this or why this is happening
>
> explain SELECT account.id, account.organization_id, run.application_id,
> work_unit.script, work_unit.id, work_unit.start_time, run.id,
> work_unit.priority FROM work_unit, run, account WHERE work_unit.status =
> 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status =
> 1 AND run.account_id = account.id;
>
>
> you need to use EXPLAIN ANALYZE to get accurate data.
>
> run it when the query is fast, and again when the query is slow, paste
> both outputs here. also, you can paste them to http://explain.depesz.com
> and that will give you a nice analysis of the timing data included in the
> EXPLAIN ANALYZE output.
>
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
