Search Postgresql Archives

Re: after vacuum analyze, explain still wrong

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux