From: Steve Crawford <scrawford@pinpointresearch.com>
To: Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query running slow for only one specific id. (Postgres 9.3) version
Date: Fri, 05 Jun 2015 11:24:34 -0700
Message-ID: <5571E962.90701@pinpointresearch.com> (raw)
In-Reply-To: <13E39D789E19FF4E9A87F6D456F3F982E86DCC@SEAMBX02.sea.corp.int.untd.com>
References: <13E39D789E19FF4E9A87F6D456F3F982E86DCC@SEAMBX02.sea.corp.int.untd.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote:
>
> Postgresql 9.3 Version
>
> Guys
>
> Here is the issue that I’m facing for couple of weeks now.
> I have table (size 7GB)
>
> *If I run this query with this specific registration id it is using
> the wrong execution plan and takes more than a minute to complete.
> Total number of rows for this registration_id is only 414 in this table*
>
> 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
>
> *Same query with any other registration id will come back in milli
> seconds *
>
> explain analyze SELECT max(last_update_date) AS last_update_date FROM
> btdt_responses WHERE registration_id = 8688546267 AND response != 4;
>
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
>
> Aggregate (cost=529.75..529.78 rows=1 width=8) (actual
> time=19.723..19.723 rows=1 loops=1)
>
> -> Index Scan using btdt_responses_u2 on btdt_responses
> (cost=0.57..529.45 rows=119 width=8) (actual time=0.097..19.689
> rows=72 loops=1)
>
> Index Cond: (registration_id = 8688546267::bigint)
>
> Filter: (response <> 4)
>
> Rows Removed by Filter: 22
>
> Total runtime: 19.769 ms
>
A couple initial questions:
1. Does the result change if you analyze the table and rerun the query?
2. Are there any non-default settings for statistics collection on your
database?
-Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, Prabhjot.Singh@classmates.com, pgsql-general@postgresql.org
Subject: Re: Query running slow for only one specific id. (Postgres 9.3) version
In-Reply-To: <5571E962.90701@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox