Re: Egroupware infolog query slow (includes query plan) - Mailing list pgsql-sql

I should have mentioned in the last post that PostgreSQL 8.2.9 is in
use. I could upgrade to 8.3.x if that is expected to help performance in
this case. 
  Mark

On Sun, 2008-07-06 at 16:23 -0400, Mark Stosberg wrote:
> Hello,
> 
> I could use some help figuring out how to speed up a query. Below is the
> SQL and query plan for a common SELECT done by the open source
> eGroupware project. 
> 
> Now that there are about 16,000 rows in egw_infolog and 32,000 in
> egw_infolog_extra, the below query takes about 6 minutes to finish!
> 
> I'm not skilled enough at reading the "Explain Analzyze" output to
> understand what the primary problem is. 
> 
> Thanks!
> 
>     Mark
> 
> ###########
> 
> SELECT DISTINCT main.* ,(
>     SELECT count(*) FROM egw_infolog sub WHERE
>     sub.info_id_parent=main.info_id AND  (info_owner=6 OR
>         ((','||info_responsible||',' LIKE '%,-2,%' OR
>                 ','||info_responsible||',' LIKE '%,-1,%' OR
>                 ','||info_responsible||',' LIKE '%,6,%') AND
>             info_access='public') OR info_owner IN (6) OR
> (info_access='public'
>             AND info_owner IN(6))) 
> ) AS info_anz_subs  FROM egw_infolog main
> LEFT JOIN egw_infolog_extra ON main.info_id=egw_infolog_extra.info_id
> WHERE (
>     (info_owner=6 OR ((','||info_responsible||',' LIKE '%,-2,%' OR
>                 ','||info_responsible||',' LIKE '%,-1,%' OR
>                 ','||info_responsible||',' LIKE '%,6,%') AND
>             info_access='public') OR info_owner IN (6) OR
> (info_access='public'
>             AND info_owner IN(6)))  AND info_status <> 'deleted'  )
> ORDER BY
> info_datemodified DESC LIMIT 15 OFFSET 0
> 
> Query plan:
> ####
> Limit  (cost=68624989.18..68624991.31 rows=15 width=1011) (actual
> time=686260.735..686260.878 rows=15 loops=1)
>   ->  Unique  (cost=68624989.18..68627288.59 rows=16212 width=1011)
> (actual time=686260.733..686260.857 rows=15 loops=1)
>         ->  Sort  (cost=68624989.18..68625068.47 rows=31716 width=1011)
> (actual time=686260.730..686260.766 rows=29 loops=1)
>               Sort Key: main.info_datemodified, main.info_id,
> main.info_type, main.info_from, main.info_addr, main.info_subject,
> main.info_des, main.info_owner, main.info_responsible, main.info_access,
> main.info_cat, main.info_startdate, main.info_enddate,
> main.info_id_parent, main.info_planned_time, main.info_used_time,
> main.info_status, main.info_confirm, main.info_modifier,
> main.info_link_id, main.info_priority, main.pl_id, main.info_price,
> main.info_percent, main.info_datecompleted, main.info_location,
> main.info_custom_from, (subplan)
>               ->  Merge Left Join  (cost=0.00..68594428.95 rows=31716
> width=1011) (actual time=21.358..684226.134 rows=32424 loops=1)
>                     Merge Cond: (main.info_id =
> egw_infolog_extra.info_id)
>                     ->  Index Scan using egw_infolog_pkey on egw_infolog
> main  (cost=0.00..3025.84 rows=16212 width=1011) (actual
> time=0.060..135.766 rows=16212 loops=1)
>                           Filter: (((info_owner = 6) OR (((((','::text
> || (info_responsible)::text) || ','::text) ~~ '%,-2,%'::text) OR
> (((','::text || (info_responsible)::text) || ','::text) ~~ '%,-1,
> %'::text) OR (((','::text || (info_responsible)::text) || ','::text) ~~
> '%,6,%'::text)) AND ((info_access)::text = 'public'::text)) OR
> (info_owner = 6) OR (((info_access)::text = 'public'::text) AND
> (info_owner = 6))) AND ((info_status)::text <> 'deleted'::text))
>                     ->  Index Scan using egw_infolog_extra_pkey on
> egw_infolog_extra  (cost=0.00..1546.30 rows=32424 width=4) (actual
> time=0.025..317.272 rows=32424 loops=1)
>                     SubPlan
>                       ->  Aggregate  (cost=2162.60..2162.61 rows=1
> width=0) (actual time=21.073..21.073 rows=1 loops=32424)
>                             ->  Seq Scan on egw_infolog sub
> (cost=0.00..2122.07 rows=16212 width=0) (actual time=21.065..21.065
> rows=0 loops=32424)
>                                   Filter: ((info_id_parent = $0) AND
> ((info_owner = 6) OR (((((','::text || (info_responsible)::text) ||
> ','::text) ~~ '%,-2,%'::text) OR (((','::text ||
> (info_responsible)::text) || ','::text) ~~ '%,-1,%'::text) OR
> (((','::text || (info_responsible)::text) || ','::text) ~~ '%,6,
> %'::text)) AND ((info_access)::text = 'public'::text)) OR (info_owner =
> 6) OR (((info_access)::text = 'public'::text) AND (info_owner = 6))))
> Total runtime: 686278.730 ms
> 
> 
> 
> 
>