Greg Stark wrote:
> Actually most of the estimates seem pretty good. There are some that
> are a ways off, but the real nasties seem to be these. I'm a bit
> confused because it looks like two of your joins don't have Join
> Filters -- and one of those is a left join for which I thought that
> was impossible.
The top half of the explain shows a lot of estimated rows=1 and actual
rows=a lot more, which is why I suspected the stats.
I left join without the filter looks like it is from:
FROM assemblies a
JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
--HERE
LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid =
b.partid
--HERE
LEFT JOIN stockperowner_lead_ab() c(partid, ownerid, stock, leadstateid)
ON c.partid = e.partid AND c.ownerid = 1 AND
leadcompcheck_ab(a.leadfree, c.leadstateid)
LEFT JOIN stocklog f ON f.refid = d.batchid AND f.transtypeid = 3 AND
f.partid = e.partid
WHERE (d.assembliesbatchstatusid = ANY (ARRAY[1, 2, 4, 7])) AND
f.commited IS NOT TRUE
GROUP BY d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname,
c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity,
a.leadfree;
it looks like it is using an index scan instead of a filter:
-> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..0.28 rows=1 width=16) (actual time=0.011..0.012 rows=1
loops=3705)
Index Cond: ((e.partid = b.partid) AND (b.assemblyid = a.assemblyid))
> Are you sure this query is doing what you expect? You have
> add_missing_from enabled which will happily bring in additional joins
> if you reference a table which isn't already included in the join and
> do a full cartesian-product join.
The results of the query are exactly what I expect them to be. I have
manually verified this on multiple occasions with users who wanted
verification that the numbers were correct.
I went through it again to verify and there are no add_missing_from
examples in here. I have that on intentionally, because I use it in
update and delete statements.