From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: pgsql-performance@postgresql.org
Subject: Re: no MCV list of tiny table with unique columns
In-reply-to: <20161102201955.GB2139@telsasoft.com>
References: <20161102185318.GA3987@telsasoft.com>
 <26162.1478117146@sss.pgh.pa.us> <20161102201955.GB2139@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Wed, 02 Nov 2016 15:19:55 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <27251.1478130503.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 02 Nov 2016 19:48:23 -0400
Message-ID: <27252.1478130503@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Justin Pryzby <pryzby@telsasoft.com> writes:
>> With only two rows in the table, I'm not real sure why you'd need an MCV
>> list.  Could we see the actual problem query (and the other table
>> schemas), rather than diving into the code first?

> Sigh, yes, but understand that it's a legacy report which happens to curr=
ently
> be near the top of my list of things to improve:
> https://explain.depesz.com/s/5rN6

Hmm, I wonder what you have join_collapse_limit and from_collapse_limit
set to.  There's an awful lot of tables in that query.

Also, it seems like most of the rowcount misestimations have to do with
inheritance child tables, eg

  Append (cost=3D0.000..50,814.990 rows=3D2,156 width=3D36) (actual time=3D=
9.054..1,026.409 rows=3D429,692 loops=3D1)
    Seq Scan on delta_mike golf_six (cost=3D0.000..0.000 rows=3D1 width=3D3=
6) (actual time=3D0.009..0.009 rows=3D0 loops=3D1)
      Filter: ((four_charlie >=3D 'alpha_six'::timestamp without time zone)=
 AND (four_charlie <=3D 'four_three'::timestamp without time zone) AND (ech=
o_tango('seven_november'::text, four_charlie) >=3D 'november_golf'::double =
precision) AND (echo_tango('seven_november'::text, four_charlie) <=3D 'papa=
_quebec'::double precision))
    Index Scan using bravo on papa_two four_delta (cost=3D0.430..50,814.990=
 rows=3D2,155 width=3D36) (actual time=3D9.043..848.063 rows=3D429,692 loop=
s=3D1)
      Index Cond: ((four_charlie >=3D 'alpha_six'::timestamp without time z=
one) AND (four_charlie <=3D 'four_three'::timestamp without time zone))
      Filter: ((echo_tango('seven_november'::text, four_charlie) >=3D 'nove=
mber_golf'::double precision) AND (echo_tango('seven_november'::text, four_=
charlie) <=3D 'papa_quebec'::double precision))

There's not a lot of point in worrying about your two-row table when these
other estimates are off by multiple orders of magnitude.  In this
particular case my first bet would be that the planner has no idea about
the selectivity of the conditions on "echo_tango('seven_november'::text,
four_charlie)".  Reformulating that, or maybe making an index on it just
so that ANALYZE will gather stats about it, could help.

			regards, tom lane


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance