Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQ7QI-0005fu-43 for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 07:30:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQ7QH-0007Vk-LK for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 07:30:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dQ7OQ-0004MW-FO for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 07:28:06 +0000 Received: from mail-qk0-x234.google.com ([2607:f8b0:400d:c09::234]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQ7OM-00075H-TA for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 07:28:06 +0000 Received: by mail-qk0-x234.google.com with SMTP id d78so43861316qkb.1 for ; Wed, 28 Jun 2017 00:28:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=pfhn8XFZQwaqgAcAWYghl7u5Tq4ogRcsR/lYrXWb374=; b=BuZI2nZyUG6Zy+rAbVqgjuyL1zSdo3ocbp193y9UXUo3+GQau+m+Px3Sr15+aDCdpD xJ0keKVfAkz+1VWuJvx8HnU4VdheGzV9eBsT+0aezlAUvzDICPguCM5F1goUL65ZQXzE tIAZ1QDvQgKD5WtGXBUosepFyx19MafWvjZZ18p1xezmn3HLWIbGybIaV2oYvKfZXz36 daEE1wTh38tr7L/52FJWW5aj4TTwqhpursmpIXbEGuYpwvKv2Jz27VJTQLHJMsPDfAIU ZopluH4BawCId8Of0zRKxhjub3V8T1nGo5ZSQnk4DdkWQdATmD+UT4TU61CFxPvQrdOb tugQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=pfhn8XFZQwaqgAcAWYghl7u5Tq4ogRcsR/lYrXWb374=; b=aZTyE0Eq580H3d3QWEYJrk40Vqfo7Nrrz9Vp3HdFcAingoS1iN3jdjH57OaMcKhHJt 6dYvRSUqhNRp5c1isAwudc1/bTNZb46JtC81SUX4sieCGKb6KVn4ti1hB9Fzf3JI9C0W FVwJsc3Ad9d1zjRuHoQW35UriWCVMkiks28xLyYjO+mcqoXQ9QK/oS7vgNupxAbc/wVi GYiUzbqcqBgah8SN579zdsm0QmQBglfyigxmgaSJEjA9y7poTlAQi+QBrmMjwT4p0HVa 3uv3yrD64oHxwBdHBOj73aD9c5SEFlUWsiYqx8+/G/UgCO5MTZkufZInLxxkeqekXvz8 fEvg== X-Gm-Message-State: AKS2vOzmnb/xsG6K1njIh/uvKxAPCbmIzwul36OEMb5qzOY/4QzSc352 bCKtYTIDLF0IaUdN/N2+Lkpc+dkQjQ== X-Received: by 10.55.169.206 with SMTP id s197mr10255217qke.115.1498634881273; Wed, 28 Jun 2017 00:28:01 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.179.16 with HTTP; Wed, 28 Jun 2017 00:28:00 -0700 (PDT) In-Reply-To: References: From: Yevhenii Kurtov Date: Wed, 28 Jun 2017 14:28:00 +0700 Message-ID: Subject: Re: To: Pavel Stehule Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c054802bf15330553001f8f" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --94eb2c054802bf15330553001f8f Content-Type: text/plain; charset="UTF-8" Hello Pavel, Can you please give a tip how to rewrite the query with UNION clause? I didn't use it at all before actually and afraid that will not get it properly from the first time :) On Wed, Jun 28, 2017 at 2:12 PM, Pavel Stehule wrote: > > > 2017-06-28 8:47 GMT+02:00 Yevhenii Kurtov : > >> Hello, >> >> We have a query that is run almost each second and it's very important to >> squeeze every other ms out of it. The query is: >> >> SELECT c0."id" FROM "campaign_jobs" AS c0 >> WHERE (((c0."status" = $1) AND NOT (c0."id" = ANY($2)))) >> OR ((c0."status" = $3) AND (c0."failed_at" > $4)) >> OR ((c0."status" = $5) AND (c0."started_at" < $6)) >> ORDER BY c0."priority" DESC, c0."times_failed" >> LIMIT $7 >> FOR UPDATE SKIP LOCKED >> >> I added following index: >> >> CREATE INDEX ON campaign_jobs(id, status, failed_at, started_at, priority >> DESC, times_failed); >> >> And it didn't help at all, even opposite - the planning phase time grew >> up from ~2ms up to ~40 ms leaving execution time intact: >> >> Limit (cost=29780.02..29781.27 rows=100 width=18) (actual >> time=827.753..828.113 rows=100 loops=1) >> -> LockRows (cost=29780.02..32279.42 rows=199952 width=18) (actual >> time=827.752..828.096 rows=100 loops=1) >> -> Sort (cost=29780.02..30279.90 rows=199952 width=18) (actual >> time=827.623..827.653 rows=100 loops=1) >> Sort Key: priority DESC, times_failed >> Sort Method: external sort Disk: 5472kB >> -> Seq Scan on campaign_jobs c0 (cost=0.00..22138.00 >> rows=199952 width=18) (actual time=1.072..321.410 rows=200000 loops=1) >> Filter: (((status = 0) AND (id <> ALL >> ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22, >> 23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42, >> 43,44,45,46,47,48}'::integer[]))) OR ((status = 2) AND (failed_at > >> '2017-06-22 03:18:09'::timestamp without time zone)) OR ((status = 1) AND >> (started_at < '2017-06-23 03:11:09'::timestamp without time zone))) >> Planning time: 40.734 ms >> Execution time: 913.638 ms >> (9 rows) >> >> >> I see that query still went through the Seq Scan instead of Index Scan. >> Is it due to poorly crafted index or because of query structure? Is it >> possible to make this query faster? >> > > There are few issues > > a) parametrized LIMIT > b) complex predicate with lot of OR > c) slow external sort > > b) signalize maybe some strange in design .. try to replace "OR" by > "UNION" query > c) if you can and you have good enough memory .. try to increase work_mem > .. maybe 20MB > > if you change query to union queries, then you can use conditional indexes > > create index(id) where status = 0; > create index(failed_at) where status = 2; > create index(started_at) where status = 1; > > Regards > > Pavel > > >> >> Thanks >> > > --94eb2c054802bf15330553001f8f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Pavel,

Can you please give a tip = how to rewrite the query with UNION clause? I didn't use it at all befo= re actually and afraid that will not get it properly from the first time :)=

On We= d, Jun 28, 2017 at 2:12 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-06-28 8:47 GMT+02:00 Yevhenii Kurtov <yevhenii.kurtov@gmail.= com>:
Hello,
We have a que= ry that is run almost each second and it's very important to squeeze ev= ery other ms out of it. The query is:=C2=A0

SELEC= T c0."id" FROM "campaign_jobs" AS c0
WHERE (((c0."statu= s" =3D $1) AND NOT (c0."id" =3D ANY($2))))
OR ((c0."status= " =3D $3) AND (c0."failed_at" > $4))
OR ((c0."status"= ; =3D $5) AND (c0."started_at" < $6))
ORDER BY c0."priority&qu= ot; DESC, c0."times_failed"
= LIMIT $7
FOR UPDATE SKIP LOCKED

I added following index:=C2=A0

CREATE INDEX ON ca= mpaign_jobs(id, status, failed_at, started_at, priority DESC, times_failed)= ;

And it didn't help at all, even opposite - the planning phase= time grew up from ~2ms =C2=A0up to ~40 ms leaving execution time intact:

=C2=A0Limit =C2=A0(cost=3D29780.02..29781.27 rows=3D100 width=3D18)= (actual time=3D827.753..828.113 rows=3D100 loops=3D1)
=C2=A0 =C2= =A0-> =C2=A0LockRows =C2=A0(cost=3D29780.02..32279.42 rows=3D199952 widt= h=3D18) (actual time=3D827.752..828.096 rows=3D100 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D29780.02..302= 79.90 rows=3D199952 width=3D18) (actual time=3D827.623..827.653 rows=3D100 = loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Sort Key: priority DESC, times_failed
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: external sort =C2=A0Disk: 5472k= B
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> = =C2=A0Seq Scan on campaign_jobs c0 =C2=A0(cost=3D0.00..22138.00 rows=3D1999= 52 width=3D18) (actual time=3D1.072..321.410 rows=3D200000 loops=3D1)
=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Filter: (((status =3D 0) AND (id <> ALL ('{1,2,3,4,5,6,7,8,= 9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,= 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48}'::inte= ger[]))) OR ((status =3D 2) AND (failed_at > '2017-06-22 03:18:= 09'::timestamp without time zone)) OR ((status =3D 1) AND (started_at &= lt; '2017-06-23 03:11:09'::timestamp without time zone)))
=C2=A0Planning time: 40.734 ms
=C2=A0Execution time: 913.638 ms<= /div>
(9 rows)


I se= e that query still went through the Seq=C2=A0Scan instead of Index Scan. Is= it due to poorly crafted index or because of query structure? Is it possib= le to make this query faster?

There are few issues=C2=A0

a) parametrized L= IMIT
b) complex predicate with lot of OR=C2=A0
c) =C2= =A0slow external sort

b) signalize maybe some stra= nge in design .. try to replace "OR" by "UNION" query
c) if you can and you have good enough memory .. try to increase w= ork_mem .. maybe 20MB

if you change query to union= queries, then you can use conditional indexes

cre= ate index(id) where status =3D 0;
create index(failed_at) where s= tatus =3D 2;
create index(started_at) where status =3D 1;

Regards

Pavel



Thanks


--94eb2c054802bf15330553001f8f--