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
Date: Wed, 02 Nov 2016 19:48:23 -0400
Message-ID: <27252.1478130503@sss.pgh.pa.us> (raw)
In-Reply-To: <20161102201955.GB2139@telsasoft.com>
References: <20161102185318.GA3987@telsasoft.com>
<26162.1478117146@sss.pgh.pa.us>
<20161102201955.GB2139@telsasoft.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 currently
> 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=0.000..50,814.990 rows=2,156 width=36) (actual time=9.054..1,026.409 rows=429,692 loops=1)
Seq Scan on delta_mike golf_six (cost=0.000..0.000 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)
Filter: ((four_charlie >= 'alpha_six'::timestamp without time zone) AND (four_charlie <= 'four_three'::timestamp without time zone) AND (echo_tango('seven_november'::text, four_charlie) >= 'november_golf'::double precision) AND (echo_tango('seven_november'::text, four_charlie) <= 'papa_quebec'::double precision))
Index Scan using bravo on papa_two four_delta (cost=0.430..50,814.990 rows=2,155 width=36) (actual time=9.043..848.063 rows=429,692 loops=1)
Index Cond: ((four_charlie >= 'alpha_six'::timestamp without time zone) AND (four_charlie <= 'four_three'::timestamp without time zone))
Filter: ((echo_tango('seven_november'::text, four_charlie) >= 'november_golf'::double precision) AND (echo_tango('seven_november'::text, four_charlie) <= '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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, pryzby@telsasoft.com
Subject: Re: no MCV list of tiny table with unique columns
In-Reply-To: <27252.1478130503@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