Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z0wIM-0004QV-VY for pgsql-performance@arkaria.postgresql.org; Fri, 05 Jun 2015 18:24:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Z0wIM-0005yu-FC for pgsql-performance@arkaria.postgresql.org; Fri, 05 Jun 2015 18:24:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Z0wIK-0005x3-VN; Fri, 05 Jun 2015 18:24:41 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1Z0wIH-0008H2-6t; Fri, 05 Jun 2015 18:24:40 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 95670E00FC0F; Fri, 5 Jun 2015 11:24:34 -0700 (PDT) Message-ID: <5571E962.90701@pinpointresearch.com> Date: Fri, 05 Jun 2015 11:24:34 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.0 To: "Sheena, Prabhjot" , "pgsql-general@postgresql.org" , "pgsql-performance@postgresql.org" Subject: Re: Query running slow for only one specific id. (Postgres 9.3) version References: <13E39D789E19FF4E9A87F6D456F3F982E86DCC@SEAMBX02.sea.corp.int.untd.com> In-Reply-To: <13E39D789E19FF4E9A87F6D456F3F982E86DCC@SEAMBX02.sea.corp.int.untd.com> Content-Type: multipart/alternative; boundary="------------080803090004060905060906" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------080803090004060905060906 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote: > > Postgresql 9.3 Version > > Guys > > Here is the issue that I=92m facing for couple of weeks now.= =20 > I have table (size 7GB) > > *If I run this query with this specific registration id it is using=20 > the wrong execution plan and takes more than a minute to complete.=20 > 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=20 > btdt_responses WHERE registration_id =3D 8718704208 AND response !=3D 4= ; > > QUERY PLAN > > -----------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------- > > Result (cost=3D2902.98..2903.01 rows=3D1 width=3D0) (actual=20 > time=3D86910.730..86910.731 rows=3D1 loops=3D1) > > InitPlan 1 (returns $0) > > -> Limit (cost=3D0.57..2902.98 rows=3D1 width=3D8) (actual=20 > time=3D86910.725..86910.725 rows=3D1 loops=3D1) > > -> Index Scan Backward using btdt_responses_n5 on=20 > btdt_responses (cost=3D0.57..6425932.41 rows=3D2214 width=3D8) (actual= =20 > time=3D86910.723..86910.723 rows=3D1 loops=3D1) > > Index Cond: (last_update_date IS NOT NULL) > > Filter: ((response <> 4) AND (registration_id =3D=20 > 8718704208::bigint)) > > Rows Removed by Filter: 52145434 > > Total runtime: 86910.766 ms > > *Same query with any other registration id will come back in milli=20 > seconds * > > explain analyze SELECT max(last_update_date) AS last_update_date FROM=20 > btdt_responses WHERE registration_id =3D 8688546267 AND response !=3D 4= ; > > QUERY PLAN > > -----------------------------------------------------------------------= ----------------------------------------------------------------------- > > Aggregate (cost=3D529.75..529.78 rows=3D1 width=3D8) (actual=20 > time=3D19.723..19.723 rows=3D1 loops=3D1) > > -> Index Scan using btdt_responses_u2 on btdt_responses =20 > (cost=3D0.57..529.45 rows=3D119 width=3D8) (actual time=3D0.097..19.689= =20 > rows=3D72 loops=3D1) > > Index Cond: (registration_id =3D 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=20 database? -Steve --------------080803090004060905060906 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote:

Postgresql 9.3 Version

=A0

Guys

=A0=A0=A0=A0=A0=A0=A0=A0=A0 Here=A0 is the= issue that I=92m facing for couple of weeks now. I have table (size =A07GB)=

=A0

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=

=A0

explain analyze SELECT max(last_update_date) AS last_update_date FROM btdt_responses WHERE registration_id =3D 8718704208 AND response !=3D 4;<= /o:p>

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN

------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------

Result=A0 (cost=3D2902.98..2903.01 rows=3D= 1 width=3D0) (actual time=3D86910.730..86910.731 rows=3D1 loops=3D= 1)

=A0=A0 InitPlan 1 (returns $0)<= /p>

=A0=A0=A0=A0 ->=A0 Limit=A0 (cost=3D0.5= 7..2902.98 rows=3D1 width=3D8) (actual time=3D86910.725..86910.725 rows=3D= 1 loops=3D1)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 In= dex Scan Backward using btdt_responses_n5 on btdt_responses=A0 (cost=3D0.57..6425932.41 rows=3D2214 width=3D8) (actual time=3D86910.723..86910.723 rows= =3D1 loops=3D1)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 Index Cond: (last_update_date IS NOT NULL)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 Filter: ((response <> 4) AND (registration_id =3D 8718704208::bigint))<= /o:p>

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 Rows Removed by Filter: 52145434

Total runtime: 86910.766 ms

=A0

=A0

Same query with any other registration id will come back in milli seconds

=A0

=A0

=A0

explain analyze SELECT max(last_update_date) AS last_update_date FROM btdt_responses WHERE registration_id =3D 8688546267 AND response !=3D 4;<= /o:p>

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 QUERY PLAN

------------------------------------------= -------------------------------------------------------------------------= ---------------------------

Aggregate=A0 (cost=3D529.75..529.78 rows=3D= 1 width=3D8) (actual time=3D19.723..19.723 rows=3D1 loops=3D1)

=A0=A0 ->=A0 Index Scan using btdt_responses_u2 on btdt_responses=A0 (cost=3D0.57..529.45 rows=3D119 width=3D8) (actual time=3D0.097..19.689 rows=3D72 lo= ops=3D1)

=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (regi= stration_id =3D 8688546267::bigint)

=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (response= <> 4)

=A0=A0=A0=A0=A0=A0=A0=A0 Rows Removed by F= ilter: 22

Total runtime: 19.769 ms

=A0

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

--------------080803090004060905060906--