Re: after vacuum analyze, explain still wrong

Sim Zacks wrote:
> Grzegorz Jaśkiewicz wrote:
>> give us postgresql version as well, maybe show query and at least table layout.
>>
>
The queries look like (The one I'm trying to run is the last one, which
is based on all the previous ones).:

CREATE OR REPLACE VIEW assembliesstockbatch AS
 SELECT d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname,
c.ownerid, e.partid, d.units, - e.quantity AS quantity,
COALESCE(c.stock, 0::bigint) AS stock, max(b.quantity) AS qtyperunit,
a.leadfree
   FROM assemblies a
   JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
   JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
   LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid
= b.partid
   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;


CREATE OR REPLACE VIEW assembliesstockbatchprioruse AS
 SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.units, a.quantity, a.stock, COALESCE(sum(-
b.quantity)::double precision, 0::double precision) AS prioruse, sum(
        CASE
            WHEN c.batchid IS NOT NULL THEN - b.quantity
            ELSE 0
        END)::double precision AS priorusebatch, a.qtyperunit, a.leadfree
   FROM assembliesstockbatch a
   LEFT JOIN (allocatedassemblies b
   JOIN assembliesbatch c ON b.assembliesbatchid = c.assembliesbatchid
AND (c.assembliesbatchstatusid = 1 OR c.assembliesbatchstatusid = 2 OR
c.assembliesbatchstatusid = 4 OR c.assembliesbatchstatusid = 7)
   JOIN assemblies q ON q.assemblyid = c.assemblyid) ON a.partid =
b.partid AND (a.ownerid IS NULL OR a.ownerid = 1) AND a.leadfree =
q.leadfree AND (a.duedate > c.duedate OR a.duedate = c.duedate AND
a.assembliesbatchid > c.assembliesbatchid)
  GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree;

CREATE OR REPLACE VIEW assembliesstockbatchpriorexpected AS
 SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.units, a.quantity, a.stock, a.prioruse,
a.priorusebatch, COALESCE(sum(
        CASE
            WHEN COALESCE(e.promisedby::timestamp without time
zone::timestamp with time zone, e.requestedby::timestamp without time
zone::timestamp with time zone,
            CASE
                WHEN e.deliverywks IS NULL THEN f.issuedate
                ELSE NULL::date
            END::timestamp without time zone::timestamp with time zone,
            CASE
                WHEN e.deliverywks <> -1 THEN (f.issuedate +
e.deliverywks * 7)::timestamp without time zone::timestamp with time zone
                ELSE a.duedate + '1 day'::interval
            END) <= a.duedate THEN COALESCE(e.quantity, 0) -
COALESCE(e.deliveredsum, 0)
            ELSE NULL::integer
        END), 0::bigint) AS expectedbefore, a.qtyperunit
   FROM assembliesstockbatchprioruse a
   LEFT JOIN (pos f
   JOIN poparts e ON f.poid = e.poid AND f.postatusid >= 20 AND
f.postatusid <= 59 AND f.isrfq = false
   JOIN manufacturerpartpn g ON g.pnid = e.pnid) ON e.partid = a.partid
  GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units,
a.qtyperunit, a.priorusebatch;

CREATE OR REPLACE VIEW assemblycanbuild AS
 SELECT assembliesstockbatchpriorexpected.assembliesbatchid,
        CASE
            WHEN min(
            CASE
                WHEN (assembliesstockbatchpriorexpected.stock::double
precision - assembliesstockbatchpriorexpected.prioruse -
assembliesstockbatchpriorexpected.quantity::double precision) >=
0::double precision THEN 100000000::double precision
                WHEN
COALESCE(assembliesstockbatchpriorexpected.qtyperunit, 0::double
precision) = 0::double precision OR
(assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) < 0::double precision THEN
0::double precision
                ELSE
trunc((assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) /
assembliesstockbatchpriorexpected.qtyperunit)
            END) = 100000000::double precision THEN 'All'::character varying
            ELSE min(
            CASE
                WHEN
COALESCE(assembliesstockbatchpriorexpected.qtyperunit, 0::double
precision) = 0::double precision OR
(assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) < 0::double precision THEN
0::double precision
                ELSE
trunc((assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) /
assembliesstockbatchpriorexpected.qtyperunit)
            END)::character varying
        END AS canbuild
   FROM assembliesstockbatchpriorexpected
  WHERE assembliesstockbatchpriorexpected.quantity <> 0
  GROUP BY assembliesstockbatchpriorexpected.assembliesbatchid;