Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQ7AA-0004ij-Dq for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 07:13:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQ7A9-0006Bq-Nh for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 07:13:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dQ7A9-0006BS-4h for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 07:13:21 +0000 Received: from mail-wm0-x22f.google.com ([2a00:1450:400c:c09::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQ7A6-0006iz-3c for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 07:13:19 +0000 Received: by mail-wm0-x22f.google.com with SMTP id t129so10195042wmt.1 for ; Wed, 28 Jun 2017 00:13:17 -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=h1pfBZLMell2syBl1jjp6y02MZi3FqJ+I0UnpTPQVz0=; b=uwPb7AUKwgfVKfGs0QxTRFyNEN+9x8oHoGZH+S3nscrOPy2SiCTqc6NQadfDSPNeGz GN62UR1+JcWyxO7y2HSCSPlYtoLrgERiZ4a2i2wEHIhwghQO++IM0bbedAMH0FlfRu9E DzpkSoStkpkVf5uH0h9vPVKpo1ZoZNuD2wkWM++A9zwEM03EYmnv64kdmHVJUNTis3XP auj3Vrzttinya6gPFfIcvIaQUrbMUuuDDD8DJ8CVVC97V3gMgE+yM8XkJRDMFWLHk9hH kqPUByhvP031N+5Y7nUprYz+3I3XtaYZSGjay3ZXVA+ddi4OaSTtptqQ+J+ylnGFFdtH d9zA== 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=h1pfBZLMell2syBl1jjp6y02MZi3FqJ+I0UnpTPQVz0=; b=ShIKdO2UTN7aXxIOqbGIXnkjp3/8j//OPjtCyfFtoBNxXCLfvG2MaBRmXTHHxCH6VZ ROV4/4HQihz5c3/s3jJYa7FAMQEfcOMCgRdi6v91QGT3uP+dHs2s36sFRj/3zBL5z2V0 xye6f+sPIAPf3Zrfk26nwkNwbXz8/BfL/8inu8q37BBt2ZQNqG5YvnznqD7S8nKx60ln v5t0VT7tnR9XeEKF2wuKtYkmvKu5sHWKD9r3QqfmO+J8R6LUKJ3r/L5FtL7/jO+DDuiC d1alcZSQhIKj6veHil1Pz91Eht73Gyz/PjpI0vQSH63tHQ0NblshysUCjdxqL3bto8bA Rw5Q== X-Gm-Message-State: AKS2vOzoeFOUnWY1s4gxFOXroSs0rOEIIbm/Mn3HPSzgcqabqaJXhbg9 R+b+WsVjEGk3CeIwW7LSxAmsrQ258w== X-Received: by 10.80.180.152 with SMTP id w24mr6689611edd.42.1498633996102; Wed, 28 Jun 2017 00:13:16 -0700 (PDT) MIME-Version: 1.0 Received: by 10.80.145.88 with HTTP; Wed, 28 Jun 2017 00:12:35 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Wed, 28 Jun 2017 09:12:35 +0200 Message-ID: Subject: Re: To: Yevhenii Kurtov Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c0df8b0fc71ff0552ffea98" 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 --94eb2c0df8b0fc71ff0552ffea98 Content-Type: text/plain; charset="UTF-8" 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 > --94eb2c0df8b0fc71ff0552ffea98 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-06-28 8:47 GMT+02:00 Yevhenii Kurtov <yevhenii.kurtov@gma= il.com>:
<= span style=3D"font-size:12.8px">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:=C2=A0

SE= LECT c0."id" FROM "campaign_jobs" AS c0
WHERE (((c0."st= atus" =3D $1) AND NOT (c0."id" =3D ANY($2))))
OR ((c0."statu= s" =3D $3) AND (c0."failed_at" > $4))
OR ((c0."status&quo= t; =3D $5) AND (c0."started_at" < $6))
ORDER BY c0."priority&q= uot; 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?

T= here are few issues=C2=A0

a) parametrized LIMIT
b) complex predicate with lot of OR=C2=A0
c) =C2=A0slow e= xternal sort

b) signalize maybe some strange in de= sign .. 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 =3D 0;
create index(failed_at) where status =3D= 2;
create index(started_at) where status =3D 1;

Regards

Pavel


Thanks

--94eb2c0df8b0fc71ff0552ffea98--