Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQer4-0000Dt-3h for pgsql-performance@arkaria.postgresql.org; Thu, 29 Jun 2017 19:11:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQer3-00034n-3o for pgsql-performance@arkaria.postgresql.org; Thu, 29 Jun 2017 19:11:53 +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 1dQer1-000337-JI for pgsql-performance@postgresql.org; Thu, 29 Jun 2017 19:11:51 +0000 Received: from mail-qk0-x232.google.com ([2607:f8b0:400d:c09::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQequ-0007o1-Ox for pgsql-performance@postgresql.org; Thu, 29 Jun 2017 19:11:50 +0000 Received: by mail-qk0-x232.google.com with SMTP id p21so84486535qke.3 for ; Thu, 29 Jun 2017 12:11:44 -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=6mz9PLkwGvJnlketyjxsuQOAMYX64XhXKod8JAoTrhU=; b=sJY9ccjF48SphQr7jvn/5Fmsk5DvUvdy5+n1rmFjLyY64ybwbgbSNIBJ6gBSD+OFQ5 dLd0PXrWMZFBoJ9M6DG09QBXjPq8XWRojhT+YDG07zz73g1j/fMPXVqy0Zb/ugl1MFui nPsORhOCWVplc/HGTX9jDIqHQ6ysEV1VgahdFbjFAmV0/+n9F6cLvsDbhbDD8V8pjyP3 d7tDRt+D6f10UIKZSSWANMVTPvd4b2W2g10oUQY2pvxrZNPNePXaeAMSZtmy09RgfQdo rq9B57CDcdaa8bpTc6yrCI1VrXAAhxGdtDZgnhDcth0FxmbPtc5z1f7KcGyhfJ5k9t4z oIWA== 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=6mz9PLkwGvJnlketyjxsuQOAMYX64XhXKod8JAoTrhU=; b=YZIZGgsR6ZYseSWCcE9ckCWskBEkc8Tu/A4zDmJFFWUah5P+Miw+oiOwruGa7GvL40 Mq5LMIry74gGfjKF2Ar02PuqPYonGLoltUk5+B5Upum5gC+iUKoteduTs9wekG/H8zv+ AS0LAhsCAm47iGPVgHl1XFZ6ae5zwfo2ClJ+Di1x7GyvxN1SoEvnn/PCKXSmk6teMp/9 sWV45A9f1yQfvznetCiXcNKZcZQh2T9HxjMn/OrN5x0dnq91vW1jI8nbx1sctTrJCyIg WLAgEM/imsUlKEeNQoSNQyHZtGN9vyQJdPxr0gjDSVCUW0N5Qe4HGtSgcTrGoD+udo3m RE4A== X-Gm-Message-State: AKS2vOxtt2/5UjHkPfgi2cjaXI6gEDRm6QbsQK5fWls9R7xgereuPhSe S9Sxpa7hJgEvadMisFP6i4VGVRPSuA== X-Received: by 10.55.147.3 with SMTP id v3mr20266398qkd.149.1498763503902; Thu, 29 Jun 2017 12:11:43 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.179.25 with HTTP; Thu, 29 Jun 2017 12:11:43 -0700 (PDT) In-Reply-To: References: From: Yevhenii Kurtov Date: Fri, 30 Jun 2017 02:11:43 +0700 Message-ID: Subject: Re: To: Jeff Janes Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c08b4fa40a5d005531e122a" 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 --94eb2c08b4fa40a5d005531e122a Content-Type: text/plain; charset="UTF-8" Hi Jeff, That is just a sample data, we are going live in Jun and I don't have anything real so far. Right now it's 9.6 and it will be a latest stable available release on the date that we go live. On Fri, Jun 30, 2017 at 1:11 AM, Jeff Janes wrote: > On Tue, Jun 27, 2017 at 11:47 PM, Yevhenii Kurtov < > yevhenii.kurtov@gmail.com> wrote: > >> 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 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? >> > > An index on (priority desc, times_failed) should speed this up massively. > Might want to include status at the end as well. However, your example data > is not terribly realistic. > > What version of PostgreSQL are you using? > > Cheers, > > Jeff > --94eb2c08b4fa40a5d005531e122a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Jeff,

That is just a sample data, we= are going live in Jun and I don't have anything real so far. Right now= it's 9.6 and it will be a latest stable available release on the date = that we go live.=C2=A0

On Fri, Jun 30, 2017 at 1:11 AM, Jeff Janes <jeff.jane= s@gmail.com> wrote:
On Tue, Jun 27, 2017 at 11:47 PM, Yevhenii Kurtov &= lt;yevhenii.= kurtov@gmail.com> wrote:
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

= SELECT c0."id" FROM "campai= gn_jobs" AS c0
WHERE (((c0."status" =3D $1) AND NOT (c0."id&= quot; =3D ANY($2))))
OR ((c0."status" =3D $3) AND (c0."failed_at&= quot; > $4))
OR ((c0."status" =3D $5) AND (c0."started_at"= ; < $6))
ORDER BY c0."priority" DESC, c0."times_failed"
LIMIT = $7
FO= R UPDATE SKIP LOCKED


=C2=A0
=

<= /div>
I see that query stil= l went through the Seq=C2=A0Scan instead of Index Scan. Is it due to poorly= crafted index or because of query structure? Is it possible to make this q= uery faster?

An index on= =C2=A0(priority desc, times_failed) should speed this up massively.=C2=A0 M= ight want to include status at the end as well. However, your example data = is not terribly realistic.

What version of Postgre= SQL are you using?

Cheers,

Jeff

--94eb2c08b4fa40a5d005531e122a--