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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
