Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQbkj-00078W-N5 for pgsql-performance@arkaria.postgresql.org; Thu, 29 Jun 2017 15:53:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQbki-0003KT-Jg for pgsql-performance@arkaria.postgresql.org; Thu, 29 Jun 2017 15:53:08 +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 1dQbis-00082T-9i for pgsql-performance@postgresql.org; Thu, 29 Jun 2017 15:51:14 +0000 Received: from mail-wr0-x229.google.com ([2a00:1450:400c:c0c::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQbin-0004TK-QH for pgsql-performance@postgresql.org; Thu, 29 Jun 2017 15:51:13 +0000 Received: by mail-wr0-x229.google.com with SMTP id 77so191773744wrb.1 for ; Thu, 29 Jun 2017 08:51:09 -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=tr6CiA31onA5L8D/+ZznkkQ45r3IvxIQMnZPhwsMoik=; b=OYr6lqXQAqUmvcU6MWPQOdZRcqJ3D4tiNh6voDaMOfZNMQO7NYZV4t2PdneEtjthsy 09Z1uAxbrxwmTsX+pZX6Hj8wUF5YoLetqQGm+3exy3AoAOY8a94isepf00ssnQZguBHW p0xjypafERAXO8DPGRC2l3w25VxPe2Kf+QvUai07bVR8R4klQfhcjlKL8hg5C/PsXeoP ekgcEEt5EjNwoDARHNsaou208PddPZLI/ILgOgtoXMRL29SDHW4LlHeHT7Mq9ukwJUkq j/l9mtd74WbGVNK4tbJFnKKSQgCGwdLkeoXx9wQS12TxhXn+sZc/u9dcTB6Sh8GmZ7+U XviA== 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=tr6CiA31onA5L8D/+ZznkkQ45r3IvxIQMnZPhwsMoik=; b=srn1A6DnZla4VsFeqbABAVcLMLqCkqahpkOV3GGKdQGhubpMSu90siUIuEeE7dnZ33 cEpceqACA1Wd0UzzSf5umaY7zP1FgyIN9k/Y2p62sbyK3herGasUBPHOOXSSvuhyAcyF g1l4SbEB8ejufSFuRwwtRrenbFjuhWmamRCCQEfeZDWJQEK20DpQT8xhqFJUztEq6GKG GfiXi8rilJWd7Gu2BYCdOKEob+4gzP5OSLp3R+EWqRmTG+WnYcoP3DcfKvnOT8jIqGwf hDPV0Qv1kiJXhtd78rqpk53uQHPLxiq6vl2DjqV5eCd5xSMv/7CU5pAzZD0IYmq8rrn1 Tilg== X-Gm-Message-State: AKS2vOypnuyd2uJDVlJvFc1qwX2WJbadpesORnrXCMcR8Q24oVHG2i0Y yL8YOIIX3fP289rDnlRftPKAAM7MQA== X-Received: by 10.223.133.186 with SMTP id 55mr25860910wrt.102.1498751468202; Thu, 29 Jun 2017 08:51:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.152.10 with HTTP; Thu, 29 Jun 2017 08:50:27 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Thu, 29 Jun 2017 17:50:27 +0200 Message-ID: Subject: Re: To: Yevhenii Kurtov Cc: Brad DeJong , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11497f24de72a305531b4457" 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 --001a11497f24de72a305531b4457 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 2017-06-29 7:17 GMT+02:00 Yevhenii Kurtov : > Hello folks, > > Thank you very much for analysis and suggested - there is a lot to learn > here. I just tried UNION queries and got following error: > > ERROR: FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT > it is sad :( maybe bitmap index scan can work postgres=3D# create table test(id int, started date, failed date, status in= t); CREATE TABLE postgres=3D# create index on test(id) where status =3D 0; CREATE INDEX postgres=3D# create index on test(started) where status =3D 1; CREATE INDEX postgres=3D# create index on test(failed ) where status =3D 2; CREATE INDEX postgres=3D# explain select id from test where (status =3D 0 and id in (1,2,3,4,5)) or (status =3D 1 and started < current_date) or (status =3D 2 = and failed > current_date); =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80 =E2=94=82 QUERY PLAN =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90 =E2=94=82 Bitmap Heap Scan on test (cost=3D12.93..22.50 rows=3D6 width=3D4= ) =E2=94=82 Recheck Cond: (((id =3D ANY ('{1,2,3,4,5}'::integer[])) AND (st= atus =3D 0)) OR ((started < CURRENT_DATE) AND (status =3D 1)) OR ((faile =E2=94=82 Filter: (((status =3D 0) AND (id =3D ANY ('{1,2,3,4,5}'::intege= r[]))) OR ((status =3D 1) AND (started < CURRENT_DATE)) OR ((status =3D 2) =E2=94=82 -> BitmapOr (cost=3D12.93..12.93 rows=3D6 width=3D0) =E2=94=82 -> Bitmap Index Scan on test_id_idx (cost=3D0.00..4.66 = rows=3D1 width=3D0) =E2=94=82 Index Cond: (id =3D ANY ('{1,2,3,4,5}'::integer[])) =E2=94=82 -> Bitmap Index Scan on test_started_idx (cost=3D0.00..= 4.13 rows=3D3 width=3D0) =E2=94=82 Index Cond: (started < CURRENT_DATE) =E2=94=82 -> Bitmap Index Scan on test_failed_idx (cost=3D0.00..4= .13 rows=3D3 width=3D0) =E2=94=82 Index Cond: (failed > CURRENT_DATE) =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80 (10 rows) > > I made a table dump for anyone who wants to give it a spin > https://app.box.com/s/464b12glmlk5o4gvzz7krc4c8s2fxlwr > and here is the gist for the original commands https://gist.github. > com/lessless/33215d0c147645db721e74e07498ac53 > > On Wed, Jun 28, 2017 at 8:10 PM, Brad DeJong > wrote: > >> >> >> On 2017-06-28, Pavel Stehule wrote ... >> > On 2017-06-28, Yevhenii Kurtov wrote ... >> >> On 2017-06-28, Pavel Stehule wrote ... >> >>> On 2017-06-28, Yevhenii Kurtov wrote ... >> >>>> 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: >> >>>> ... >> >>>> I added following index: CREATE INDEX ON campaign_jobs(id, status, >> failed_at, started_at, priority DESC, times_failed); >> >>>> ... >> >>> 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 =3D 0; >> >>> create index(failed_at) where status =3D 2; >> >>> create index(started_at) where status =3D 1; >> >> >> >> Can you please give a tip how to rewrite the query with UNION clause? >> > >> > SELECT c0."id" FROM "campaign_jobs" AS c0 >> > WHERE (((c0."status" =3D $1) AND NOT (c0."id" =3D ANY($2)))) >> > UNION SELECT c0."id" FROM "campaign_jobs" AS c0 >> > WHERE ((c0."status" =3D $3) AND (c0."failed_at" > $4)) >> > UNION SELECT c0."id" FROM "campaign_jobs" AS c0 >> > WHERE ((c0."status" =3D $5) AND (c0."started_at" < $6)) >> > ORDER BY c0."priority" DESC, c0."times_failed" >> > LIMIT $7 >> > FOR UPDATE SKIP LOCKED >> >> >> Normally (at least for developers I've worked with), that kind of query >> structure is used when the "status" values don't overlap and don't chang= e >> from query to query. Judging from Pavel's suggested conditional indexes >> (i.e. "where status =3D "), he also thinks that is likely. >> >> Give the optimizer that information so that it can use it. Assuming $1 = =3D >> 0 and $3 =3D 2 and $5 =3D 1, substitute literals. Substitute literal for= $7 in >> limit. Push order by and limit to each branch of the union all (or does >> Postgres figure that out automatically?) Replace union with union all (n= ot >> sure about Postgres, but allows other dbms to avoid sorting and merging >> result sets to eliminate duplicates). (Use of UNION ALL assumes that "id= " >> is unique across rows as implied by only "id" being selected with FOR >> UPDATE. If multiple rows can have the same "id", then use UNION to >> eliminate the duplicates.) >> >> SELECT "id" FROM "campaign_jobs" WHERE "status" =3D 0 AND NOT "id" =3D A= NY($1) >> UNION ALL >> SELECT "id" FROM "campaign_jobs" WHERE "status" =3D 2 AND "failed_at" > = $2 >> UNION ALL >> SELECT "id" FROM "campaign_jobs" WHERE "status" =3D 1 AND "started_at" <= $3 >> ORDER BY "priority" DESC, "times_failed" >> LIMIT 100 >> FOR UPDATE SKIP LOCKED >> >> >> Another thing that you could try is to push the ORDER BY and LIMIT to th= e >> branches of the UNION (or does Postgres figure that out automatically?) = and >> use slightly different indexes. This may not make sense for all the >> branches but one nice thing about UNION is that each branch can be tweak= ed >> independently. Also, there are probably unmentioned functional dependenc= ies >> that you can use to reduce the index size and/or improve your match rate= . >> Example - if status =3D 1 means that the campaign_job has started but no= t >> failed or completed, then you may know that started_at is set, but >> failed_at and ended_at are null. The < comparison in and of itself impli= es >> that only rows where "started_at" is not null will match the condition. >> >> SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE (((c0."status" =3D 0) AN= D >> NOT (c0."id" =3D ANY($1)))) ORDER BY c0."priority" DESC, c0."times_faile= d" >> LIMIT 100 >> UNION ALL >> SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE ((c0."status" =3D 2) AND >> (c0."failed_at" > $2)) ORDER BY c0."priority" DESC, c0."times_failed" LI= MIT >> 100 >> UNION ALL >> SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE ((c0."status" =3D 1) AND >> (c0."started_at" < $3)) ORDER BY c0."priority" DESC, c0."times_failed" >> LIMIT 100 >> ORDER BY c0."priority" DESC, c0."times_failed" >> LIMIT 100 >> FOR UPDATE SKIP LOCKED >> >> Including the "priority", "times_failed" and "id" columns in the indexes >> along with "failed_at"/"started_at" allows the optimizer to do index onl= y >> scans. (May still have to do random I/O to the data page to determine tu= ple >> version visibility but I don't think that can be eliminated.) >> >> create index ... ("priority" desc, "times_failed", "id") >> where "status" =3D 0; >> create index ... ("priority" desc, "times_failed", "id", "failed_at") >> where "status" =3D 2 and "failed_at" is not null; >> create index ... ("priority" desc, "times_failed", "id", "started_at") >> where "status" =3D 1 and "started_at" is not null; -- and ended_at is nu= ll >> and ... >> >> >> I'm assuming that the optimizer knows that "where status =3D 1 and >> started_at < $3" implies "and started_at is not null" and will consider = the >> conditional index. If not, then the "and started_at is not null" needs t= o >> be explicit. >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or= g >> ) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > --001a11497f24de72a305531b4457 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-06-29 7:17 GMT+02:00 Yevhenii Kurtov <yevhenii.kurtov@gma= il.com>:
Hello folks,

Thank you very much for a= nalysis and suggested - there is a lot to learn here. I just =C2=A0tried UN= ION queries and got following error:

ERROR: =C2=A0FOR UPDATE is not = allowed with UNION/INTERSECT/EXCEPT

it is sad :(

maybe bitmap index scan can = work

postgres=3D# create table test(id in= t, started date, failed date, status int);
CREATE TABLE
postgres=3D# create index on test(id) where status =3D 0;
CREATE INDEX
postgres=3D# create index on test(st= arted) where status =3D 1;
CREATE INDEX
post= gres=3D# create index on test(failed ) where status =3D 2;
CREATE INDEX
postgres=3D# explain select id from test where = (status =3D 0 and id in (1,2,3,4,5)) or (status =3D 1 and started < curr= ent_date) or (status =3D 2 and failed > current_date);
= =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Q= UERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0=C2=A0
=E2= =95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
=E2=94=82 Bitmap Heap Scan = on test =C2=A0(cost=3D12.93..22.50 rows=3D6 width=3D4) =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0=C2=A0
=E2=94=82 = =C2=A0 Recheck Cond: (((id =3D ANY ('{1,2,3,4,5}'::integer[])) AND = (status =3D 0)) OR ((started < CURRENT_DATE) AND (status =3D 1)) OR ((fa= ile
=E2=94=82 =C2=A0 F= ilter: (((status =3D 0) AND (id =3D ANY ('{1,2,3,4,5}'::integer[]))= ) OR ((status =3D 1) AND (started < CURRENT_DATE)) OR ((status =3D 2)
=E2=94=82 =C2=A0 -> = =C2=A0BitmapOr =C2=A0(cost=3D12.93..12.93 rows=3D6 width=3D0) =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> = =C2=A0Bitmap Index Scan on test_id_idx =C2=A0(cost=3D0.00..4.66 rows=3D1 wi= dth=3D0) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Cond: (id =3D ANY ('{1,2,3,4,5}'::integer[])) =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0
=E2=94= =82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Bitmap Index Scan on test_start= ed_idx =C2=A0(cost=3D0.00..4.13 rows=3D3 width=3D0) =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=E2=94=82 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (started < CURRENT_DATE)= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 -> =C2=A0Bitmap Index Scan on test_failed_idx =C2=A0(cost=3D0.00.= .4.13 rows=3D3 width=3D0) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 Index Cond: (failed > CURRENT_DATE) =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80
(10 rows)

=C2=A0=C2=A0

I made a table dump for anyone who wants to give it a spin= =C2=A0https://app.box.com/s/464b12glmlk5o4gvzz7krc4c8s2fxlwr
and here is the gist for the original commands=C2=A0https://gist.github.com/lessless/33215d0c147645= db721e74e07498ac53

On Wed, Jun 28, 2017 at 8:10 PM, Brad DeJong <Br= ad.Dejong@infor.com> wrote:


On 2017-06-28, Pavel Stehule wrote ...
> On 2017-06-28, Yevhenii Kurtov wrote ...
>> On 2017-06-28, Pavel Stehule wrote ...
>>> On 2017-06-28, Yevhenii Kurtov wrote ...
>>>> We have a query that is run almost each second and i= t's very important to squeeze every other ms out of it. The query is: >>>> ...
>>>> I added following index: CREATE INDEX ON campaign_jo= bs(id, status, failed_at, started_at, priority DESC, times_failed);
>>>> ...
>>> 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 &q= uot;OR" by "UNION" query
>>> c) if you can and you have good enough memory .. try to increa= se work_mem .. maybe 20MB
>>>
>>> if you change query to union queries, then you can use conditi= onal indexes
>>>
>>> create index(id) where status =3D 0;
>>> create index(failed_at) where status =3D 2;
>>> create index(started_at) where status =3D 1;
>>
>> Can you please give a tip how to rewrite the query wi= th UNION clause?
>
> SELECT c0."id" FROM "campaign_jobs" A= S c0
> WHERE (((c0."status" =3D $1) AND NOT (c0."id" =3D = ANY($2))))
> UNION SELECT c0."id" FROM "campaign_jobs" AS c0 > WHERE ((c0."status" =3D $3) AND (c0."failed_at" &g= t; $4))
> UNION SELECT c0."id" FROM "campaign_jobs" AS c0 > WHERE ((c0."status" =3D $5) AND (c0."started_at" &= lt; $6))
> ORDER BY c0."priority" DESC, c0."times_failed"
> LIMIT $7
> FOR UPDATE SKIP LOCKED


Normally (at least for developers I've worked with), that kind o= f query structure is used when the "status" values don't over= lap and don't change from query to query. Judging from Pavel's sugg= ested conditional indexes (i.e. "where status =3D <constant>&quo= t;), he also thinks that is likely.

Give the optimizer that information so that it can use it. Assuming $1 =3D = 0 and $3 =3D 2 and $5 =3D 1, substitute literals. Substitute literal for $7= in limit. Push order by and limit to each branch of the union all (or does= Postgres figure that out automatically?) Replace union with union all (not= sure about Postgres, but allows other dbms to avoid sorting and merging re= sult sets to eliminate duplicates). (Use of UNION ALL assumes that "id= " is unique across rows as implied by only "id" being select= ed with FOR UPDATE. If multiple rows can have the same "id", then= use UNION to eliminate the duplicates.)

SELECT "id" FROM "campaign_jobs" WHERE "status&quo= t; =3D 0 AND NOT "id" =3D ANY($1)
=C2=A0 UNION ALL
SELECT "id" FROM "campaign_jobs" WHERE "status&quo= t; =3D 2 AND "failed_at" > $2
=C2=A0 UNION ALL
SELECT "id" FROM "campaign_jobs" WHERE "status&quo= t; =3D 1 AND "started_at" < $3
ORDER BY "priority" DESC, "times_failed"
LIMIT 100
FOR UPDATE SKIP LOCKED


Another thing that you could try is to push the ORDER BY and LIMIT to the b= ranches of the UNION (or does Postgres figure that out automatically?) and = use slightly different indexes. This may not make sense for all the branche= s but one nice thing about UNION is that each branch can be tweaked indepen= dently. Also, there are probably unmentioned functional dependencies that y= ou can use to reduce the index size and/or improve your match rate. Example= - if status =3D 1 means that the campaign_job has started but not failed o= r completed, then you may know that started_at is set, but failed_at and en= ded_at are null. The < comparison in and of itself implies that only row= s where "started_at" is not null will match the condition.

SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE (((c0.&= quot;status" =3D 0) AND NOT (c0."id" =3D ANY($1)))) ORDER BY= c0."priority" DESC, c0."times_failed" LIMIT 100
UNION ALL
SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE ((c0.&q= uot;status" =3D 2) AND (c0."failed_at" > $2)) ORDER BY c0= ."priority" DESC, c0."times_failed" LIMIT 100
UNION ALL
SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE ((c0.&q= uot;status" =3D 1) AND (c0."started_at" < $3)) ORDER BY c= 0."priority" DESC, c0."times_failed" LIMIT 100
ORDER BY c0."priority" DESC, c0."times_failed" LIMIT 100
FOR UPDATE SKIP LOCKED

Including the "priority", "times_failed" and "id&q= uot; columns in the indexes along with "failed_at"/"started_= at" allows the optimizer to do index only scans. (May still have to do= random I/O to the data page to determine tuple version visibility but I do= n't think that can be eliminated.)

create index ... ("priority" desc, "times_failed", &quo= t;id")=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where &qu= ot;status" =3D 0;
create index ... ("priority" desc, "times_failed", &quo= t;id", "failed_at")=C2=A0 where "status" =3D 2 and= "failed_at" is not null;
create index ... ("priority" desc, "times_failed", &quo= t;id", "started_at") where "status" =3D 1 and &quo= t;started_at" is not null; -- and ended_at is null and ...


I'm assuming that the optimizer knows that "where status =3D 1 and= started_at < $3" implies "and started_at is not null" an= d will consider the conditional index. If not, then the "and started_a= t is not null" needs to be explicit.
=
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance


--001a11497f24de72a305531b4457--