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.
Date: Thu, 08 Aug 2019 18:45:07 -0400
Message-ID: <27100.1565304307@sss.pgh.pa.us> (raw)
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>
"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
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, thomas.rosenstein@creamfinance.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using correct indices for views.
In-Reply-To: <27100.1565304307@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