On Mon, Sep 5, 2016 at 6:53 PM, Patrick B <patrickbake...@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. > > > 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
