Slow CTE Query

[Stefan Keller <sfkeller@xxxxxxxxx>]

Hi,

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?

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