Re: Help with slow query - Pgsql 9.2

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Help with slow query - Pgsql 9.2
Дата
Msg-id CAMkU=1yvYTwtuAFKq9nvVs-ti1TFXyYG=sBWAWNZ795xMMw2gw@mail.gmail.com
обсуждение исходный текст
Ответ на Help with slow query - Pgsql 9.2  (Patrick B <patrickbakerbr@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Mon, Sep 5, 2016 at 6:53 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I got this query:
SELECT id,jobid,description,serialised_data
FROM logtable
WHERE log_type = 45
AND clientid = 24011
ORDER BY gtime desc


What is really going to help you here is multicolumn index on (clientid, log_type), or (log_type, clientid).

It will not cost you much, because you can get rid of whichever single-column index is on the column you list first in your multi-column index. 



So it seems the very slow part is into:

              ->  Bitmap Index Scan on "ix_client"  (cost=0.00..5517.96 rows=367593 width=0) (actual time=2668.246..2668.246 rows=356327 loops=1)
                    Index Cond: ("clientid" = 24011)

Am I right? The query is already using an index on that table... how could I improve the performance in a query that is already using an index?

Right, that is the slow step.  Probably the index is not already in memory and had to be read from disk, slowly.  You could turn track_io_timing on and then run explain (analyze, buffers) to see if that is the case.  But once you build a multi-column index, it shouldn't really matter anymore.
 
Cheers,

Jeff

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

Предыдущее
От: Naveed Shaikh
Дата:
Сообщение: Re: PostgreSQL Database performance
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: PostgreSQL Database performance