From: Tom Lane <tgl@sss.pgh.pa.us>
To: Duncan Whitham <dwhitham@zaizi.com>
cc: Fahiz Mohamed <fahiz@netwidz.com>, Jeff Janes <jeff.janes@gmail.com>,
        Michael Lewis <mlewis@entrata.com>,
        Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Specific query taking time to process
In-reply-to: 
 <CABaJvbHm5cuQ=oo3H2w7RjmfUe-9RJwfr-gR6wnY3Az8ksmMTQ@mail.gmail.com>
References: <6bd9b9b7-f127-4e48-a456-b3bbcfb741ab@Spark>
 <d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark>
 <CAHOFxGqYS=FV31Dv6PNDVpAGAHHO3mSMKd5M1yOJFjoUis5GtQ@mail.gmail.com>
 <10b65cf1-a079-4aa3-92f0-ca21375da683@Spark>
 <CAHOFxGp0mh6fx=VKc2tcx__fCYH5KZVORhm9fJDqLCAfB-7CvQ@mail.gmail.com>
 <7cb455c5-dd6a-4f33-bdbe-92033f7986e2@Spark>
 <CAMkU=1wD6J_NAGRot-F8cNgnfEDaMu5n_K7x4rbQW5GKd00Z4Q@mail.gmail.com>
 <53622ec9-bf97-4066-ac52-9b8e5698c3ea@Spark>
 <CABaJvbHm5cuQ=oo3H2w7RjmfUe-9RJwfr-gR6wnY3Az8ksmMTQ@mail.gmail.com>
Comments: In-reply-to Duncan Whitham <dwhitham@zaizi.com>
	message dated "Thu, 30 Jan 2020 12:22:58 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <22538.1580402150.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 30 Jan 2020 11:35:50 -0500
Message-ID: <22539.1580402150@sss.pgh.pa.us>
Precedence: bulk

Duncan Whitham <dwhitham@zaizi.com> writes:
> We now only need 1 environment as we  can replicate the performance prob=
lem
> on a copy of live =E2=80=93 snapshot/restore from AWS of live. We now ha=
ve a vacuum
> analyse running every night on the 3 tables in question on live =E2=80=93=
 to
> eliminate bloat and inaccurate stats as the root of the problem.

Hmm, doesn't seem like that's getting the job done.  I can see at
least one serious misestimate in these plans:

>                        ->  Bitmap Heap Scan on alf_node_aspects aspect_1
> (cost=3D3420.59..418372.63 rows=3D163099 width=3D8) (actual time=3D1.402=
..5.243
> rows=3D4909 loops=3D1)
>                              Recheck Cond: (qname_id =3D 251)

It doesn't seem to me that such a simple condition ought to be
misestimated by a factor of 30, so either you need to crank up
the stats target for this column or you need to analyze the
table more often.

The other rowcount estimates don't seem so awful, but this one is
contributing to the planner thinking that "SubPlan 1" is going to
be very expensive, which probably accounts for it trying to avoid
what's actually a cheap plan.

			regards, tom lane