Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQ6lT-0003C1-S7 for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 06:47:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQ6lS-0004NT-4Z for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 06:47:50 +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 1dQ6lQ-0004It-Q7 for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 06:47:48 +0000 Received: from mail-qt0-x236.google.com ([2607:f8b0:400d:c0d::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQ6lO-0006DN-6N for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 06:47:47 +0000 Received: by mail-qt0-x236.google.com with SMTP id f92so42193192qtb.2 for ; Tue, 27 Jun 2017 23:47:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=Y2Bqjdwho0OQRGCxJT/+2ghCv75d7kmibglVvB/ZxLU=; b=sA3TEmI/pj23FUmCC6blXxRZxk73Q8R8FCOSaN7DaXaJTUbInlD6FJIspclITzUfvM +2FbLdR2rxWsOI1LuZ2RV+X8LiDTOUHt/PU8lOlqgsPanMmqfjzgRpo7chiYZXGICYKG oolAgnplR/agxTMwzJIMm3g4DSWYUDBKtdCUOMyrM6iTGzHdYeVJVaIYDtSIEUlpR8fC YyUqJT0/dPjrGUGZGP2TX/bUWDg4lr5iK3fTlPVPt/ERtL1O8ursJkJNjUvV5/8+WRwF RbwsxHg6Kne47CeMjfFqqVGubBTZgCuOktBiyS6NfeQJK1J3wuTcQU8KaQtqSnzp9pe5 UDgw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=Y2Bqjdwho0OQRGCxJT/+2ghCv75d7kmibglVvB/ZxLU=; b=JEZbDPDnsyej7F6nbUsyPrH+m5vIhq9gkoCmij6cwSIp4WeFRUt46SajhR28WfnefM HSEDb5ZgsLKfuRKu+c6G+7zahZTuTws58/qwEevrPlV3Y3txU4VQLC602mTsNM7VPkvI OppV3F/+4Ad5yPjd35fQyL93+00UNlzT/rkOGXoVwQKmnoq9t/Z5xElut0gVHt+2M6Dc qLPv3z7wq+tShThvW/ncnqrFgjnht2n5hUqGQf72koNXTsPb2K0T7q6azhP+4XYk9LYS sTdgTRbEwdBMCKOCeQjHWj3NQbKFhnzCwlK31zdjOIoRqOmqmp7XHafJiF5+Ll7wpqPd PJqA== X-Gm-Message-State: AKS2vOzHzT+q7VOjolw9dmnW18MChsyXYEuZw8yR72FBDxssltd3qJgr PjS3MpMa0Xdu9s9mNzKESn+L+zdOON4v X-Received: by 10.200.3.213 with SMTP id z21mr5800190qtg.185.1498632464564; Tue, 27 Jun 2017 23:47:44 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.179.16 with HTTP; Tue, 27 Jun 2017 23:47:44 -0700 (PDT) From: Yevhenii Kurtov Date: Wed, 28 Jun 2017 13:47:44 +0700 Message-ID: Subject: To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="f4030435b22cb306f10552ff8ff9" 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 --f4030435b22cb306f10552ff8ff9 Content-Type: text/plain; charset="UTF-8" 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? Thanks --f4030435b22cb306f10552ff8ff9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,

We have a query that is run almost each second and it's very im= portant to squeeze every other ms out of it. The query is:=C2=A0

SELECT c0."id" FROM "campaign_jobs" AS = c0
WH= ERE (((c0."status" =3D $1) AND NOT (c0."id" =3D ANY($2)= )))
O= R ((c0."status" =3D $3) AND (c0."failed_at" > $4))
OR ((c= 0."status" =3D $5) AND (c0."started_at" < $6))
ORDER BY c= 0."priority" DESC, c0."times_failed"
LIMIT $7
FOR UPDATE SKIP LOC= KED
I added following index:=C2=A0

= CREATE INDEX ON campaign_jobs(id, status, failed_at, started_at, priority D= ESC, 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 execut= ion time intact:

=C2=A0Limit =C2=A0(cost=3D29780.02..29781.27 row= s=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 width=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(c= ost=3D29780.02..30279.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: 5472kB
=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..2213= 8.00 rows=3D199952 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}'::integer[]))) OR ((status =3D 2) AND (failed_at > '= ;2017-06-22 03:18:09'::timestamp without time zone)) OR ((status =3D 1)= AND (started_at < '2017-06-23 03:11:09'::timestamp without time= zone)))
=C2=A0Planning time: 40.734 ms
=C2=A0Execution= time: 913.638 ms
(9 rows)


I see that query still went through the Seq=C2=A0Scan instead= of Index Scan. Is it due to poorly crafted index or because of query struc= ture? Is it possible to make this query faster?


Thanks
--f4030435b22cb306f10552ff8ff9--