Message-ID: <52308CE3.6040800@dunslane.net>
Date: Wed, 11 Sep 2013 11:31:47 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130110 Thunderbird/17.0.2
MIME-Version: 1.0
To: Maximilian Tyrtania <lists@contactking.de>
CC: pgsql-performance@postgresql.org
Subject: Re: slow sort
References: <4EF4FC5C-9E4A-4C88-A080-6CC671B99AA6@contactking.de>
In-Reply-To: <4EF4FC5C-9E4A-4C88-A080-6CC671B99AA6@contactking.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 09/11/2013 06:36 AM, Maximilian Tyrtania wrote:
> Hi there,
>
> here is another one from the "why is my query so slow?" category. First p=
ost, so please bare with me.
>
> The query (which takes around 6 seconds) is this:
>
> SET work_mem TO '256MB';//else sort spills to disk
>
> SELECT
> 	et.subject,
> 	COALESCE (createperson.vorname || ' ', '') || createperson.nachname AS "=
Sender/Empf=E4nger",
> 	to_char(es.sentonat, 'DD.MM.YY') AS "versendet am",
> 	es.sentonat AS orderbydate,
> 	COUNT (ct.*),
> 	COALESCE (C . NAME, 'keine Angabe') :: TEXT AS "f=FCr Kunde",
> 	COUNT (ct.datetimesentonat) :: TEXT || ' von ' || COUNT (ct.*) :: TEXT |=
| ' versendet',
> 	1 AS LEVEL,
> 	TRUE AS hassubs,
> 	FALSE AS opensubs,
> 	'emailsendings:' || es. ID :: TEXT AS model_id,
> 	NULL :: TEXT AS parent_model_id,
> 	es. ID
> FROM
> 	emailtemplates et
> JOIN emailsendings es ON et. ID =3D es.emailtemplate_id
> LEFT JOIN companies C ON C . ID =3D es.customers_id
> LEFT JOIN personen createperson ON createperson. ID =3D et.personen_creat=
e_id
> LEFT JOIN contacts ct ON ct.emailsendings_id =3D es. ID WHERE f_record_vi=
sible_to_currentuser(et.*::coretable) =3D true
> GROUP BY
> 	1,
> 	2,
> 	3,
> 	4,
> 	6,
> 	8,
> 	9,
> 	10,
> 	11,
> 	12,
> 	13
> ORDER BY
> 	es.sentonat desc
>
> Explain analyze:
>
> GroupAggregate  (cost=3D35202.88..45530.77 rows=3D118033 width=3D142) (ac=
tual time=3D5119.783..5810.680 rows=3D898 loops=3D1)
>    ->  Sort  (cost=3D35202.88..35497.96 rows=3D118033 width=3D142) (actua=
l time=3D5119.356..5200.457 rows=3D352744 loops=3D1)
>          Sort Key: es.sentonat, et.subject, ((COALESCE((createperson.vorn=
ame || ' '::text), ''::text) || createperson.nachname)), (to_char(es.senton=
at, 'DD.MM.YY'::text)), ((COALESCE(c.name, 'keine Angabe'::character varyin=
g))::text), (1), (true), (false), (('emailsendings:'::text || (es.id)::text=
)), (NULL::text), es.id
>          Sort Method:  quicksort  Memory: 198999kB
>          ->  Nested Loop Left Join  (cost=3D0.00..25259.29 rows=3D118033 =
width=3D142) (actual time=3D1.146..1896.382 rows=3D352744 loops=3D1)
>                ->  Nested Loop Left Join  (cost=3D0.00..2783.16 rows=3D30=
2 width=3D102) (actual time=3D1.127..32.577 rows=3D898 loops=3D1)
>                      ->  Merge Join  (cost=3D0.00..2120.06 rows=3D302 wid=
th=3D86) (actual time=3D1.125..30.940 rows=3D898 loops=3D1)
>                            Merge Cond: (et.id =3D es.emailtemplate_id)
>                            ->  Nested Loop Left Join  (cost=3D0.00..2224.=
95 rows=3D277 width=3D74) (actual time=3D1.109..27.484 rows=3D830 loops=3D1)
>                                  ->  Index Scan using emailtemplates_pkey=
 on emailtemplates et  (cost=3D0.00..460.71 rows=3D277 width=3D63) (actual =
time=3D1.097..20.541 rows=3D830 loops=3D1)
>                                        Filter: f_record_visible_to_curren=
tuser((et.*)::coretable)
>                                  ->  Index Scan using personen_pkey on pe=
rsonen createperson  (cost=3D0.00..6.36 rows=3D1 width=3D19) (actual time=
=3D0.006..0.006 rows=3D1 loops=3D830)
>                                        Index Cond: (createperson.id =3D e=
t.personen_create_id)
>                            ->  Index Scan using fki_emailsendings_emailte=
mplate_id_fkey on emailsendings es  (cost=3D0.00..49.83 rows=3D905 width=3D=
20) (actual time=3D0.011..1.360 rows=3D898 loops=3D1)
>                      ->  Index Scan using firmen_pkey on companies c  (co=
st=3D0.00..2.18 rows=3D1 width=3D24) (actual time=3D0.001..0.001 rows=3D0 l=
oops=3D898)
>                            Index Cond: (c.id =3D es.customers_id)
>                ->  Index Scan using fki_contacts_emailsendings_id_fkey on=
 contacts ct  (cost=3D0.00..61.55 rows=3D561 width=3D44) (actual time=3D0.0=
19..0.738 rows=3D393 loops=3D898)
>                      Index Cond: (ct.emailsendings_id =3D es.id)
> Total runtime: 5865.886 ms
>
> I do have an index on es.sentonat. The sentonat-values are all unique, so=
 I don't think I need indexes on all the fields I sort by. But then again, =
my understanding of this might be entirely wrong.
>
> Depeszs' explain (http://explain.depesz.com/s/69O) tells me this:
>
> node type	count	sum of times	% of query
> GroupAggregate	1	610.223 ms	10.5 %
> Index Scan	5	690.503 ms	11.9 %
> Merge Join	1	2.096 ms	0.0 %
> Nested Loop Left Join	3	1203.783 ms	20.7 %
> Sort	1	3304.075 ms	56.9 %
>
> , so the sort appears to be the problem. Any pointers would be highly app=
reciated.
>

I recently had to diagnose and remedy a case such as this.

The short answer is to rewrite your query so you don't have to group by=20
so many things. Collect your aggregates in a common table expression=20
query (or possibly more than one, depends what you need) using the=20
minimum non-aggregated columns to enable you to get correct results and=20
then later decorate that with all the extra things you need such as=20
constant columns and columns that are irrelevant to the aggregation.

This gets hard when queries are very complex, and harder still when the=20
query is written by a query generator. But a good generator should not=20
just say "grouo by everything that's not aggregated" and think it's=20
doing a good job. In your case it should be relatively straightforward.

cheers

andrew


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance