Date: Wed, 28 Jun 2017 09:08:54 -0400 (EDT)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Yevhenii Kurtov <yevhenii.kurtov@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <1209632247.77753.1498655334706.JavaMail.zimbra@fmed.uba.ar>
In-Reply-To: 
 <CAJhrTGxffyaiu1JwhYkY3noy5ukiyREpzv5yT_5CGubVzMXmqw@mail.gmail.com>
References: 
 <CAJhrTGxffyaiu1JwhYkY3noy5ukiyREpzv5yT_5CGubVzMXmqw@mail.gmail.com>
Subject: Re: 
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: [PERFORM]
Thread-Index: gkngcLHqLm1CSEIYmtTrujkOpS/vSw==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


----- Mensaje original -----
> De: "Yevhenii Kurtov" <yevhenii.kurtov@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Mi=C3=A9rcoles, 28 de Junio 2017 3:47:44
> Asunto: [PERFORM]
>=20
> Hello,
>=20
> 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:
>=20
> SELECT c0."id" FROM "campaign_jobs" AS c0
> WHERE (((c0."status" =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" DESC, c0."times_failed"
> LIMIT $7
> FOR UPDATE SKIP LOCKED
>=20
> I added following index:
>=20
> CREATE INDEX ON campaign_jobs(id, status, failed_at, started_at, priority
> DESC, times_failed);
>=20
> 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:
>=20
>  Limit  (cost=3D29780.02..29781.27 rows=3D100 width=3D18) (actual
> time=3D827.753..828.113 rows=3D100 loops=3D1)
>    ->  LockRows  (cost=3D29780.02..32279.42 rows=3D199952 width=3D18) (ac=
tual
> time=3D827.752..828.096 rows=3D100 loops=3D1)
>          ->  Sort  (cost=3D29780.02..30279.90 rows=3D199952 width=3D18) (=
actual
> time=3D827.623..827.653 rows=3D100 loops=3D1)
>                Sort Key: priority DESC, times_failed
>                Sort Method: external sort  Disk: 5472kB
>                ->  Seq Scan on campaign_jobs c0  (cost=3D0.00..22138.00
> rows=3D199952 width=3D18) (actual time=3D1.072..321.410 rows=3D200000 loo=
ps=3D1)
>                      Filter: (((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) A=
ND
> (started_at < '2017-06-23 03:11:09'::timestamp without time zone)))
>  Planning time: 40.734 ms
>  Execution time: 913.638 ms
> (9 rows)
>=20
>=20
> 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?
>=20
>=20
> Thanks
>=20
Well, most of the time is spent ordering, and it is doing a (slow) disk sor=
t. Try increasing work_mem for a in-memory sort.

How many rows in campaign_jobs? If the query is returning most of the rows =
in the table, it will not going to use any index anyway.

HTH
Gerardo


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance