Re: bad plan using nested loops

2018-02-01 Thread Tom Lane
Johan Fredriksson writes: > Bad plan: https://explain.depesz.com/s/avtZ > Good plan: https://explain.depesz.com/s/SJSt > Any suggestions on how to make the planner make better decisions for > this query? Core of the problem looks to be the misestimation here: Index Only Scan using shredd

Re: Bad plan

2018-01-23 Thread Matthew Bellew
In my opinion this is the Achilles heel of the postgres optimizer. Row estimates should never return 1, unless the estimate is provably <=1. This is particularly a problem with join estimates. A dumb fix for this is to change clamp_join_row_est() to never return a value <2. This fixes most of m

Re: Bad plan

2018-01-23 Thread Laurent Martelli
I've have a look to the plan with pgadmin, and I think the problem is rather here : -> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896 rows=7359 loops=1) Sort Key: amendment.id Sort Method: quicksort Memory: 1227kB -> Nested Loop (cost=183.44..4997.1

Re: Bad plan

2018-01-23 Thread Laurent Martelli
2018-01-23 16:18 GMT+01:00 Justin Pryzby : > On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote: > >> Here is the default plan : > > Can you resend without line breaks or paste a link to explain.depesz? I hope it's better like that. I've attached it too, just in case. > > The proble

Re: Bad plan

2018-01-23 Thread Justin Pryzby
On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote: > Hello all, > > So I have a view, for which I can select all rows in about 3s (returns ~80k > rows), but if I add a where clause on a column, it takes +300s to return > the ~8k lines. > > From the plan, I see that it expects to re

Re: Bad plan for ltree predicate <@

2017-12-01 Thread Roman Konoval
Hi Tom, Thanks for your help. > On Dec 1, 2017, at 22:33, Tom Lane wrote: > > > The seqscan formulation of the query results in evaluating > this function afresh at most of the rows The function is defined as STABLE. I though that means that there is no need to reevaluate it on every row as

Re: Bad plan for ltree predicate <@

2017-12-01 Thread Tom Lane
Roman Konoval writes: > I have a problem on 9.3.14 with a query that accesses table: I think the root of the problem is your intermediate function: > CREATE OR REPLACE FUNCTION public.get_doc_path(document_id character varying) > RETURNS ltree > LANGUAGE plpgsql > STABLE > AS $function$ > DEC

Re: Bad plan chosen for union all

2017-11-28 Thread Alex Reece
One more thing. Given this: > The difference here is that, from the perspective of the outer query, > the WHERE condition is a restriction clause on the "cim" relation, > not a join clause. So it will get pushed down into the subquery > without creating any join order constraints on the outer qu

Re: Bad plan chosen for union all

2017-11-28 Thread Tom Lane
Alex Reece writes: > I managed to reduce my test case: the following query does not take > advantage of the index on contribution metrics. Yeah. What you're wishing is that the planner would push a join condition down into a subquery, but it won't do that at present. Doing so would require gener

Re: Bad plan chosen for union all

2017-11-28 Thread Alex Reece
I managed to reduce my test case: the following query does not take advantage of the index on contribution metrics. explain select cim.yield from earnings JOIN contributions on contributions.id = earnings.note_id JOIN ( SELECT contribution_id, max(CASE metrics.name WHEN 'Yield'::text THEN proj