Re: Slow CTE Query - Mailing list pgsql-performance

On Sat, May 18, 2013 at 12:54 PM, Stefan Keller <sfkeller@gmail.com> 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@postgresql.org)
> 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@gmail.com