On Sat, May 18, 2013 at 12:54 PM, Stefan Keller <sfkeller@xxxxxxxxx> wrote: > I'm experiencing a very slow CTE query (see below). > > When I split the three aggregations into three separate views, its' decent > fast. So I think it's due to the planner. > > Any ideas like reformulating the query? Rewrite it without CTE. Planner will have more freedom in this case. Also I would try to use LEFT JOIN ... IS NULL technique instead of NOT EXISTS. > > These are the tables and views involved: > * Table promotion with start/end date and a region, and table > promo2mission (each 1 to dozen tupels). > * View all_errors (more than 20'000 tubles, based on table errors > without tupels from table fix) > * Table error_type (7 tupels) > > Here's the EXPLAIN ANALYZE log: http://explain.depesz.com/s/tbF > > Yours, Stefan > > > CTE Query: > > WITH aggregation1 > AS (SELECT p.id AS promo_id, > p.startdate, > p.enddate, > p.geom AS promogeom, > pm.error_type, > pm.mission_extra_coins AS extra_coins > FROM (promotion p > join promo2mission pm > ON (( p.id = pm.promo_id ))) > WHERE ( ( p.startdate <= Now() ) > AND ( p.enddate >= Now() ) )), > aggregation2 > AS (SELECT e.error_id AS missionid, > e.schemaid, > t.TYPE, > e.osm_id, > e.osm_type, > t.description AS title, > t.view_type, > t.answer_placeholder, > t.bug_question AS description, > t.fix_koin_count, > t.vote_koin_count, > e.latitude, > e.longitude, > e.geom AS missiongeom, > e.txt1, > e.txt2, > e.txt3, > e.txt4, > e.txt5 > FROM all_errors e, > error_type t > WHERE ( ( e.error_type_id = t.error_type_id ) > AND ( NOT ( EXISTS (SELECT 1 > FROM fix f > WHERE ( ( ( ( f.error_id = e.error_id ) > AND ( f.osm_id = > e.osm_id ) ) > AND ( ( f.schemaid ) :: text = > ( e.schemaid ) :: text ) ) > AND ( ( f.complete > AND f.valid ) > OR ( NOT > f.complete ) ) )) ) ) )), > aggregation3 > AS (SELECT ag2.missionid AS missionidtemp, > ag1.promo_id, > ag1.extra_coins > FROM (aggregation2 ag2 > join aggregation1 ag1 > ON (( ( ag2.TYPE ) :: text = ( ag1.error_type ) :: text ))) > WHERE public._st_contains(ag1.promogeom, ag2.missiongeom)) > SELECT ag2.missionid AS id, > ag2.schemaid, > ag2.TYPE, > ag2.osm_id, > ag2.osm_type, > ag2.title, > ag2.description, > ag2.latitude, > ag2.longitude, > ag2.view_type, > ag2.answer_placeholder, > ag2.fix_koin_count, > ag2.missiongeom, > ag2.txt1, > ag2.txt2, > ag2.txt3, > ag2.txt4, > ag2.txt5, > ag3.promo_id, > ag3.extra_coins > FROM (aggregation2 ag2 > left join aggregation3 ag3 > ON (( ag2.missionid = ag3.missionidtemp ))); > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
