Re: [PERFORM]

Поиск
Список
Период
Сортировка
От Gerardo Herzig
Тема Re: [PERFORM]
Дата
Msg-id 1209632247.77753.1498655334706.JavaMail.zimbra@fmed.uba.ar
обсуждение исходный текст
Ответ на [PERFORM]  (Yevhenii Kurtov <yevhenii.kurtov@gmail.com>)
Список pgsql-performance

----- Mensaje original -----
> De: "Yevhenii Kurtov" <yevhenii.kurtov@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Miércoles, 28 de Junio 2017 3:47:44
> Asunto: [PERFORM]
>
> 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
>
Well, most of the time is spent ordering, and it is doing a (slow) disk sort. 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
indexanyway. 

HTH
Gerardo


В списке pgsql-performance по дате отправления:

Предыдущее
От: Ulf Lohbrügge
Дата:
Сообщение: Re: [PERFORM] Performance of information_schema with many schemataand tables
Следующее
От: Brad DeJong
Дата:
Сообщение: Re: [PERFORM]