Postgres Pro
Downloads
Home   >   mailing lists

Re: Help with slow query - Pgsql 9.2 - Mailing list pgsql-general

From Jeff Janes
Subject Re: Help with slow query - Pgsql 9.2
Date
Msg-id CAMkU=1yvYTwtuAFKq9nvVs-ti1TFXyYG=sBWAWNZ795xMMw2gw@mail.gmail.com
Whole thread Raw
In response to Help with slow query - Pgsql 9.2  (Patrick B <patrickbakerbr@gmail.com>)
List pgsql-general
Tree view
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 by date:

Previous
From: Naveed Shaikh
Date:
Subject: Re: PostgreSQL Database performance
Next
From: Scott Marlowe
Date:
Subject: Re: PostgreSQL Database performance