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

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=6OR       ((','||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_pkeyon
 
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.61rows=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