On Mon, Sep 5, 2016 at 6:53 PM, Patrick B 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.
>
>
> Explain analyze: https://explain.depesz.com/s/XKtU
>
> 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