Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dwWAS-0002Ih-Of for pgsql-performance@arkaria.postgresql.org; Mon, 25 Sep 2017 16:23:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dwWAS-0001wV-BK for pgsql-performance@arkaria.postgresql.org; Mon, 25 Sep 2017 16:23:36 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dwW8g-00062Q-EH for pgsql-performance@postgresql.org; Mon, 25 Sep 2017 16:21:46 +0000 Received: from mail-oi0-x234.google.com ([2607:f8b0:4003:c06::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dwW8d-0003SX-A6 for pgsql-performance@postgresql.org; Mon, 25 Sep 2017 16:21:45 +0000 Received: by mail-oi0-x234.google.com with SMTP id i128so7700348oih.6 for ; Mon, 25 Sep 2017 09:21:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=rQyshjAfrN2aELOnR1SRNmCkQntaFRJbvoBzv0AYaqk=; b=NuVJujvW+XQ8Es2isntL0/xjtcdIfUvHX9A8b2ll5GDK6E3EmZDOYIRDcwIXMSi7Er pHb8Xkf3Fpqn2n3VJ30zFqW381qJpQ9/cTtsUbq4CwKQtWKNNkGw/MRWt1NApPTw+DGH W1iM4LsEamUPhhkOne8eP3oC7Guzb2fn5eASkcIZBjO9zSQDYmV2RFKAW3VdQaUSS6jD 9Zr3BnQbAYOj2yTq+K5UD5/fkHrFPS1j1mMx+aoE+QpZYgJ+MGV8Oh0KBcxHVhQ4x0Nz 4Q+dfF2gghq0XhTK7buDBcUmALfSL8V5jICZd57G5zIZ7TDHGt7z3ZBe/t5bK88nCQRR bngg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=rQyshjAfrN2aELOnR1SRNmCkQntaFRJbvoBzv0AYaqk=; b=VFNgscTgwoOohMkUwdhNm7Vv30YZoaS9L2XkVoueCuS2jcy7yobQaBLwi2ujQoZ+Bc zI5DqL/fB08U3X0papVnrVRwxCSeVOO7Ssh4oabf2opRR80ux3DrJ7LqWRmjtzukPgSN C51kn6bXX3e4Yn7VGkTIYX88YUTILWffFg5uUYEA5eNbT2CwyxIxrQPIZUPUU5Kv9h4h aungiEYolbr8aL/RHMVRL63pFBfwrTTthgUpI8wuzA/H32+/TXgJ80xu21whjNb6c0d6 ETC9j3uCCHrUNFk4WlqI/wzSaTXSqZRPbuRMHXm6Tc5OBX92RQ6dvMZxKbzSd5hUssR7 Le+A== X-Gm-Message-State: AHPjjUhw4FzdobAJg7K83Zowlvpn57zqgo1/39rw7qblrYvNzJoGvaD/ 7/CgjGU3+U9T7SflxSW3coG2oHhB5gDOksNbUyZJ34Jl X-Google-Smtp-Source: AOwi7QAW7K6+DBX+O/eHXVo159XgS5ZcqRY5CIo7D6o42QbkUHIJFp3R+oTPxJGpTSo6CiVahpWpzpVxkS+bk+WVl+4= X-Received: by 10.157.34.229 with SMTP id y92mr495330ota.225.1506356502128; Mon, 25 Sep 2017 09:21:42 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.52.73 with HTTP; Mon, 25 Sep 2017 09:21:41 -0700 (PDT) In-Reply-To: References: <3138.1505508143@sss.pgh.pa.us> From: Mike Broers Date: Mon, 25 Sep 2017 11:21:41 -0500 Message-ID: Subject: Re: query of partitioned object doesnt use index in qa To: David Rowley Cc: Tom Lane , postgres performance list Content-Type: multipart/alternative; boundary="94eb2c11e54236efca055a05f4cd" 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 --94eb2c11e54236efca055a05f4cd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Very helpful thank you for the additional insight - I'd never checked into pg_stats and that does reveal a difference in the distribution of the validation_status_code between qa and production: prod: =E2=94=82 most_common_vals =E2=94=82 {P,F} =E2=94=82 =E2=94=82 most_common_freqs =E2=94=82 {0.925967,0.000933333} =E2=94=82 =E2=94=82 histogram_bounds =E2=94=82 =E2=9D=8F = =E2=94=82 =E2=94=82 correlation =E2=94=82 0.995533 =E2=94=82 qa: =E2=94=82 most_common_vals =E2=94=82 {P} =E2=94=82 =E2=94=82 most_common_freqs =E2=94=82 {0.861633} =E2=94=82 =E2=94=82 histogram_bounds =E2=94=82 =E2=9D=8F =E2=94=82 =E2=94=82 correlation =E2=94=82 0.999961 =E2=94=82 so the way I am reading this is that there is likely no sensible way to avoid postgres thinking it will just have to scan the whole table because of these statistics. I can force it by setting session parameters for this particular query but I probably shouldnt be looking at system settings to brutally force random fetches. thanks again for the assistance! On Wed, Sep 20, 2017 at 6:05 PM, David Rowley wrote: > On 21 September 2017 at 04:15, Mike Broers wrote: > > Ultimately I think this is just highlighting the need in my environment > to > > set random_page_cost lower (we are on an SSD SAN anyway..), but I dont > think > > I have a satisfactory reason by the row estimates are so bad in the QA > > planner and why it doesnt use that partition index there. > > Without the index there are no stats to allow the planner to perform a > good estimate on "e.body->>'SID' is not null", so it applies a default > of 99.5%. So, as a simple example, if you have a partition with 1 > million rows. If you apply 99.5% to that you get 995000 rows. Now if > you add the selectivity for "e.validation_status_code =3D 'P' ", let's > say that's 50%, the row estimate for the entire WHERE clause would be > 497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both > cases, then the only variable part is validation_status_code. Perhaps > validation_status_code =3D 'P' is much more common in QA than in > production. > > You can look at the stats as gathered by ANALYZE with: > > \x on > select * from pg_stats where tablename =3D 'event__99999999' and attname > =3D 'validation_status_code'; > \x off > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > --94eb2c11e54236efca055a05f4cd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Very helpful thank you for the additional insight - I'= d never checked into pg_stats and that does reveal a difference in the dist= ribution of the validation_status_code between qa and production:

<= /div>
prod:
=E2=94=82 most_common_vals =C2=A0 =C2=A0 =C2= =A0 =E2=94=82 {P,F} =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0=E2=94=82
=E2=94=82 most_common_freqs =C2=A0 =C2=A0 =C2=A0= =E2=94=82 {0.925967,0.000933333} =E2=94=82
=E2=94=82 histogram_bo= unds =C2=A0 =C2=A0 =C2=A0 =E2=94=82 =E2=9D=8F =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=94=82
=E2=94= =82 correlation =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=94=82 0.995533= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=94=82

qa:
= =E2=94=82 most_common_vals =E2=94=82 {P} =E2=94=82=C2=A0
=E2=94=82 most_common_freqs =E2=94=82 {0.861633} =E2=94=82=C2=A0=E2=94=82 histogram_bounds =E2=94=82 =E2=9D=8F =E2=94=82=C2=A0
=E2=94=82 correlation =E2=94=82 0.999961 =E2=94=82=C2=A0

so the way I am reading this is that the= re is likely no sensible way to avoid postgres thinking it will just have t= o scan the whole table because of these statistics.=C2=A0 I can force it by= setting session parameters for this particular query but I probably should= nt be looking at system settings to brutally force random fetches.

thanks again for the assistance!

=

O= n Wed, Sep 20, 2017 at 6:05 PM, David Rowley <david.rowley@2nd= quadrant.com> wrote:
On 21 September 2017 at 04:15, Mike Broers <mbroers@gmail.com> wrote:
> Ultimately I think this is just highlighting the need in my environmen= t to
> set random_page_cost lower (we are on an SSD SAN anyway..), but I dont= think
> I have a satisfactory reason by the row estimates are so bad in the QA=
> planner and why it doesnt use that partition index there.

Without the index there are no stats to allow the planner to perform= a
good estimate on "e.body->>'SID' is not null", so i= t applies a default
of 99.5%. So, as a simple example, if you have a partition with 1
million rows. If you apply 99.5% to that you get 995000 rows. Now if
you add the selectivity for "e.validation_status_code =3D 'P' = ", let's
say that's 50%, the row estimate for the entire WHERE clause would be 497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both
cases, then the only variable part is validation_status_code. Perhaps
validation_status_code=C2=A0 =3D 'P' is much more common in QA than= in
production.

You can look at the stats as gathered by ANALYZE with:

\x on
select * from pg_stats where tablename =3D 'event__99999999' and at= tname
=3D 'validation_status_code';
\x off

--
=C2=A0David Rowley=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0http://www.2ndQuadrant.com/
=C2=A0PostgreSQL Development, 24x7 Support, Training & Services

--94eb2c11e54236efca055a05f4cd--