Re: Slow CTE Query

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