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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
