pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Duncan Whitham <dwhitham@zaizi.com>
Cc: Fahiz Mohamed <fahiz@netwidz.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Specific query taking time to process
Date: Thu, 30 Jan 2020 11:35:50 -0500
Message-ID: <22539.1580402150@sss.pgh.pa.us> (raw)
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>

Duncan Whitham <dwhitham@zaizi.com> writes:
> We now only need 1 environment as we  can replicate the performance problem
> on a copy of live – snapshot/restore from AWS of live. We now have a vacuum
> analyse running every night on the 3 tables in question on live – 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=3420.59..418372.63 rows=163099 width=8) (actual time=1.402..5.243
> rows=4909 loops=1)
>                              Recheck Cond: (qname_id = 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





view thread (14+ messages)  latest in thread

Message-ID: <22539.1580402150@sss.pgh.pa.us>
Permalink:  ../22539.1580402150@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/22539.1580402150@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, dwhitham@zaizi.com, fahiz@netwidz.com, jeff.janes@gmail.com, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
  Subject: Re: Specific query taking time to process
  In-Reply-To: <22539.1580402150@sss.pgh.pa.us>

* 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