From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Thomas Rosenstein" <thomas.rosenstein@creamfinance.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using correct indices for views.
In-reply-to: <F99F7963-C6CF-4E70-9D1E-FD1E601340B4@creamfinance.com>
References: <EC37F8FB-D294-409A-97CC-72C7A5A6CE42@creamfinance.com>
 <31061.1565280312@sss.pgh.pa.us>
 <F99F7963-C6CF-4E70-9D1E-FD1E601340B4@creamfinance.com>
Comments: In-reply-to "Thomas Rosenstein" <thomas.rosenstein@creamfinance.com>
	message dated "Thu, 08 Aug 2019 22:04:31 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <27099.1565304307.1@sss.pgh.pa.us>
Date: Thu, 08 Aug 2019 18:45:07 -0400
Message-ID: <27100.1565304307@sss.pgh.pa.us>
Precedence: bulk

"Thomas Rosenstein" <thomas.rosenstein@creamfinance.com> writes:
> The planner estimates the correct row counts, but still does the wrong 
> planning.

Hm, I'm not exactly convinced.  You show

> Wrong:
>           ->  Hash Join  (cost=359555.11..1849150.95 rows=1496816 
> width=1508) (actual time=1081.081..24251.466 rows=543231 loops=1)
>                 Hash Cond: (p.customer_id = l.customer_id)
>                 Join Filter: ((p.date - '3 days'::interval day) <= 
> l.duedate)
>                 Rows Removed by Join Filter: 596120

> Correct:
>     ->  Nested Loop  (cost=0.87..2961441.25 rows=515233 width=1471)

The join size estimate seems a lot closer to being correct in the
second case, which could lend support to the idea that statistics
aren't being applied in the first case.

However ... it sort of looks like the planner didn't even consider
the second plan shape in the "wrong" case.  If it had, then even
if it costed it 3X more than it did in the "right" case, the second
plan would still have won out by orders of magnitude.  So there's
something else going on.

Can you show the actual query and table and view definitions?

			regards, tom lane