Query/database optimization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello, I have a query:

explain analyze select tu.url_id, tu.url, coalesce(sd.recurse, 100), case when
COALESCE(get_option('use_banner')::integer,0) = 0 then 0 else ts.use_banner
end as use_banner, ts.use_cookies, ts.use_robots,  ts.includes, ts.excludes,
ts.track_domain, ts.task_id,get_available_pages(ts.task_id,ts.customer_id),
ts.redirects from task_url tu  inner join task_scheduler ts on
tu.task_id=ts.task_id  inner join  (subscription s inner join subscription_dic
sd  on sd.id=s.dict_id )  on s.customer_id=ts.customer_id  inner join customer
c on c.customer_id=ts.customer_id AND c.active  WHERE
get_available_pages(ts.task_id,ts.customer_id) > 0 AND
((get_option('expired_users')::integer = 0) OR (isfinite(last_login) AND
extract('day' from current_timestamp - last_login)::integer <=
coalesce(get_option('expired_users')::integer,100))) AND  ((s.status is null
AND ts.customer_id is null)  OR s.status > 0) AND
(get_check_period(ts.task_id,ts.next_check) is null OR
(unix_timestamp(get_check_period(ts.task_id,ts.next_check)) -
unix_timestamp(timenow()) < 3600)) AND ts.status <> 1 AND ((ts.start_time <
current_time AND ts.stop_time > current_time)  OR (ts.start_time is null AND
ts.stop_time is null))  AND tu.url_id = 1  AND ts.customer_id not in (select
distinct customer_id from task_scheduler where status = 1)  order by
ts.next_check is not null, unix_timestamp(ts.next_check) -
unix_timestamp(timenow()) limit 10;

which produces this query plan:
 Limit  (cost=2874.98..2874.99 rows=2 width=88) (actual time=11800.535..11800.546 rows=3 loops=1)
   ->  Sort  (cost=2874.98..2874.99 rows=2 width=88) (actual time=11800.529..11800.532 rows=3 loops=1)
         Sort Key: (ts.next_check IS NOT NULL), (date_part('epoch'::text, ts.next_check) - date_part('epoch'::text, (timenow())::timestamp without time zone))
         ->  Nested Loop  (cost=4.37..2874.97 rows=2 width=88) (actual time=10249.115..11800.486 rows=3 loops=1)
               ->  Nested Loop  (cost=4.37..2868.87 rows=2 width=55) (actual time=10247.721..11796.303 rows=3 loops=1)
                     Join Filter: ("inner".id = "outer".dict_id)
                     ->  Nested Loop  (cost=2.03..2865.13 rows=2 width=55) (actual time=10247.649..11796.142 rows=3 loops=1)
                           Join Filter: ((("inner".status IS NULL) AND ("outer".customer_id IS NULL)) OR ("inner".status > 0))
                           ->  Nested Loop  (cost=2.03..2858.34 rows=2 width=55) (actual time=10247.583..11795.936 rows=3 loops=1)
                                 ->  Seq Scan on customer c  (cost=0.00..195.71 rows=231 width=4) (actual time=0.082..154.344 rows=4161 loops=1)
                                       Filter: (active AND isfinite(last_login) AND ((date_part('day'::text, (('now'::text)::timestamp(6) with time zone - (last_login)::timestamp with time zone)))::integer <= 150))
                                 ->  Index Scan using task_scheduler_icustomer_id on task_scheduler ts  (cost=2.03..11.51 rows=1 width=51) (actual time=2.785..2.785 rows=0 loops=4161)
                                       Index Cond: ("outer".customer_id = ts.customer_id)
                                       Filter: ((get_available_pages(task_id, customer_id) > 0) AND ((get_check_period(task_id, next_check) IS NULL) OR ((date_part('epoch'::text, get_check_period(task_id, next_check)) - date_part('epoch'::text, (timenow())::timestamp without time zone)) < 3600::double precision)) AND (status <> 1) AND ((((start_time)::time with time zone < ('now'::text)::time(6) with time zone) AND ((stop_time)::time with time zone > ('now'::text)::time(6) with time zone)) OR ((start_time IS NULL) AND (stop_time IS NULL))) AND (NOT (hashed subplan)))
                                       SubPlan
                                         ->  Unique  (cost=2.02..2.03 rows=1 width=4) (actual time=0.617..0.631 rows=3 loops=1)
                                               ->  Sort  (cost=2.02..2.03 rows=1 width=4) (actual time=0.613..0.617 rows=3 loops=1)
                                                     Sort Key: customer_id
                                                     ->  Index Scan using task_scheduler_istatus on task_scheduler  (cost=0.00..2.01 rows=1 width=4) (actual time=0.044..0.580 rows=3 loops=1)
                                                           Index Cond: (status = 1)
                           ->  Index Scan using subscription_icustomer_id on subscription s  (cost=0.00..3.38 rows=1 width=12) (actual time=0.035..0.041 rows=1 loops=3)
                                 Index Cond: ("outer".customer_id = s.customer_id)
                     ->  Materialize  (cost=2.34..2.65 rows=31 width=8) (actual time=0.008..0.027 rows=6 loops=3)
                           ->  Seq Scan on subscription_dic sd  (cost=0.00..2.31 rows=31 width=8) (actual time=0.013..0.034 rows=6 loops=1)
               ->  Index Scan using task_url_storage_task_id on task_url tu  (cost=0.00..3.03 rows=1 width=37) (actual time=0.028..0.045 rows=1 loops=3)
                     Index Cond: (tu.task_id = "outer".task_id)
                     Filter: (url_id = 1)
 Total runtime: 11801.082 ms
(28 rows)


Do I need to optimize a query somehow, or it is related to database
configuration?

I'm running postgresql 8.0.0 on CentOS release 3.7

-- 
Eugene N Dzhurinsky


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux