Postgres Pro
Downloads
Home   >   mailing lists

Re: Query running slow for only one specific id. (Postgres 9.3) version - Mailing list pgsql-performance

From Matheus de Oliveira
Subject Re: Query running slow for only one specific id. (Postgres 9.3) version
Date
Msg-id CAJghg4K+LyPTVLNt2LL9hTwthhacLAeSa7R9tBYU6H-Lp41qaQ@mail.gmail.com
Whole thread Raw
In response to Query running slow for only one specific id. (Postgres 9.3) version  ("Sheena, Prabhjot" <Prabhjot.Singh@classmates.com>)
List pgsql-performance
Tree view

On Fri, Jun 5, 2015 at 2:54 PM, Sheena, Prabhjot <Prabhjot.Singh@classmates.com> wrote:

explain analyze SELECT max(last_update_date) AS last_update_date FROM btdt_responses WHERE registration_id = 8718704208 AND response != 4;

                                                                                QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Result  (cost=2902.98..2903.01 rows=1 width=0) (actual time=86910.730..86910.731 rows=1 loops=1)

   InitPlan 1 (returns $0)

     ->  Limit  (cost=0.57..2902.98 rows=1 width=8) (actual time=86910.725..86910.725 rows=1 loops=1)

           ->  Index Scan Backward using btdt_responses_n5 on btdt_responses  (cost=0.57..6425932.41 rows=2214 width=8) (actual time=86910.723..86910.723 rows=1 loops=1)

                 Index Cond: (last_update_date IS NOT NULL)

                 Filter: ((response <> 4) AND (registration_id = 8718704208::bigint))

                 Rows Removed by Filter: 52145434

Total runtime: 86910.766 ms


The issue here is the "Row Removed by Filter", you are filtering out more than 52M rows, so the index is not being much effective.

What you want for this query is a composite index on (registration_id, last_update_date). And if the filter always include `response <> 4`, then you can also create a partial index with that (unless it is not very selective, then it might not be worthy it).

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

pgsql-performance by date:

Previous
From: Steve Crawford
Date:
Subject: Re: Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version
Next
From: "ben.play"
Date:
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)