Hi,

I'm experiencing a very slow CTE query (see below).

When I split the three aggregationns into separate views, its' decent
fast. So I think it's due to the planner.

Any ideas how to reformulate the query?

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 )));