Date: Sat, 5 Nov 2016 17:36:37 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: no MCV list of tiny table with unique columns
Message-ID: <20161105223637.GJ2139@telsasoft.com>
References: <20161102185318.GA3987@telsasoft.com>
 <26162.1478117146@sss.pgh.pa.us>
 <20161102201955.GB2139@telsasoft.com>
 <27252.1478130503@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <27252.1478130503@sss.pgh.pa.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, Nov 02, 2016 at 07:48:23PM -0400, Tom Lane wrote:

> 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.

Thanks, you're exactly right.  That's date_trunc('hour') BTW.

We actually already have a "new way" of doing that which avoids date_trunc, so
now I just have to get it in place for 100+ old reports..

I thought I had tried that before, but I think I was confusing myself, and
tried putting the index on the parent, which ends up with no stats since it's
empty.

With indices+analyze:
 Sort  (cost=189014.28..189014.28 rows=1 width=785) (actual time=25063.831..25063.886 rows=328 loops=1)
 ...

BTW:
join_collapse_limit | 8
from_collapse_limit | 8

..and changing them doesn't seem to have any effect.  By my count there's 11
tables, not counting multiply a few used multiply..

Thanks again.

Justin


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